SnowConvert AI - Redshift - Conditions

BETWEEN

描述

BETWEEN 条件使用关键字 BETWEEN 和 AND 测试表达式是否包含在某个值范围中。（Redshift SQL 语言参考 BETWEEN 条件 (https://docs.aws.amazon.com/redshift/latest/dg/r_range_condition.html)）

语法

 expression [ NOT ] BETWEEN expression AND expression

Note

Snowflake 完全支持此函数。

示例源模式

设置表

Redshift
 CREATE TABLE sales (
    id INTEGER IDENTITY(1,1),
    price FLOAT,
    departmentId INTEGER,
    saleDate DATE
);

INSERT INTO sales (price, departmentId, saleDate) VALUES
(5000, 1, '2008-01-01'),
(8000, 1, '2018-01-01'),
(5000, 2, '2010-01-01'),
(7000, 3, '2010-01-01'),
(5000, 1, '2018-01-01'),
(4000, 4, '2010-01-01'),
(3000, 4, '2018-01-01'),
(9000, 5, '2008-01-01'),
(7000, 5, '2018-01-01'),
(6000, 5, '2006-01-01'),
(5000, 5, '2008-01-01'),
(5000, 4, '2018-01-01'),
(8000, 3, '2006-01-01'),
(7000, 3, '2016-01-01'),
(2000, 2, '2018-01-01');
Snowflake
 CREATE TABLE sales (
    id INTEGER IDENTITY(1,1) ORDER,
    price FLOAT,
    departmentId INTEGER,
    saleDate DATE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/08/2025",  "domain": "test" }}';

INSERT INTO sales (price, departmentId, saleDate) VALUES
(5000, 1, '2008-01-01'),
(8000, 1, '2018-01-01'),
(5000, 2, '2010-01-01'),
(7000, 3, '2010-01-01'),
(5000, 1, '2018-01-01'),
(4000, 4, '2010-01-01'),
(3000, 4, '2018-01-01'),
(9000, 5, '2008-01-01'),
(7000, 5, '2018-01-01'),
(6000, 5, '2006-01-01'),
(5000, 5, '2008-01-01'),
(5000, 4, '2018-01-01'),
(8000, 3, '2006-01-01'),
(7000, 3, '2016-01-01'),
(2000, 2, '2018-01-01');
输入代码：
Redshift
 SELECT COUNT(*) FROM sales
WHERE departmentId BETWEEN 2 AND 4;

SELECT * FROM sales
WHERE departmentId BETWEEN 4 AND 2;

SELECT * FROM sales
WHERE departmentId NOT BETWEEN 4 AND 2;

SELECT * FROM sales
WHERE departmentId BETWEEN 2 AND 4
AND saleDate BETWEEN '2010-01-01' and '2016-01-01';

select 'some ' between c_start and c_end 
from( select 'same' as c_start, 'some' as c_end );
Results

count

8

id

price

departmentid

saledate

id

price

departmentid

saledate

1

5000

1

2008-01-01

2

8000

1

2018-01-01

3

5000

2

2010-01-01

4

7000

3

2010-01-01

5

5000

1

2018-01-01

6

4000

4

2010-01-01

7

3000

4

2018-01-01

8

9000

5

2008-01-01

9

7000

5

2018-01-01

10

6000

5

2006-01-01

11

5000

5

2008-01-01

12

5000

4

2018-01-01

13

8000

3

2006-01-01

14

7000

3

2016-01-01

15

2000

2

2018-01-01

id

price

departmentid

saledate

3

5000

2

2010-01-01

4

7000

3

2010-01-01

6

4000

4

2010-01-01

14

7000

3

2016-01-01
输出代码：
Snowflake
 SELECT COUNT(*) FROM
    sales
WHERE departmentId BETWEEN 2 AND 4;

SELECT * FROM
    sales
WHERE departmentId BETWEEN 4 AND 2;

SELECT * FROM
    sales
WHERE departmentId NOT BETWEEN 4 AND 2;

SELECT * FROM
    sales
WHERE departmentId BETWEEN 2 AND 4
AND saleDate BETWEEN '2010-01-01' and '2016-01-01';

select
    RTRIM( 'some ') between c_start and c_end
from( select 'same' as c_start, 'some' as c_end );
Results

count

8

id

price

departmentid

saledate

id

price

departmentid

saledate

1

5000

1

2008-01-01

2

8000

1

2018-01-01

3

5000

2

2010-01-01

4

7000

3

2010-01-01

5

5000

1

2018-01-01

6

4000

4

2010-01-01

7

3000

4

2018-01-01

8

9000

5

2008-01-01

9

7000

5

2018-01-01

10

6000

5

2006-01-01

11

5000

5

2008-01-01

12

5000

4

2018-01-01

13

8000

3

2006-01-01

14

7000

3

2016-01-01

15

2000

2

2018-01-01

id

price

departmentid

saledate

3

5000

2

2010-01-01

4

7000

3

2010-01-01

6

4000

4

2010-01-01

14

7000

3

2016-01-01

已知问题

未发现任何问题。

比较条件

Conditions

描述

Comparison conditions state logical relationships between two values. All comparison conditions are binary operators with a Boolean return type.

(RedShift SQL Language Reference Comparison Condition (https://docs.aws.amazon.com/redshift/latest/dg/r_comparison_condition.html))

语法

Redshift 支持下表中描述的比较运算符：

OperatorSyntaxDescription
<a < bValue a is less than value b.
>a > bValue a is greater than value b.
<=a <= bValue a is less than or equal to value b.
>=a >= bValue a is greater than or equal to value b.
=a = bValue a is equal to value b.
<> | !=a <> b | a != bValue a is not equal to value b.
ANY | SOMEa = ANY(subquery)Value a is equal to any value returned by the subquery.
ALLa <> ALL or != ALL (subquery)Value a is not equal to any value returned by the subquery.
IS TRUE | FALSE | UNKNOWNa IS TRUEValue a is Boolean TRUE.

对字符串使用比较运算符

值得注意的是，在 Redshift 中，对字符串使用比较运算符时会忽略末尾空格。为了在 Snowflake 中复制这种行为，转换会应用 RTRIM 函数来移除末尾空格，从而确保功能等效。有关更多信息，请参阅：末尾空格的意义 (https://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html#r_Character_types-significance-of-trailing-blanks)

转换表

大多数运算符都由 Snowflake 直接支持；但是，以下运算符需要转换：

RedshiftSnowflakeComments
(expression) IS TRUEexpressionCondition is TRUE.
(expression) IS FALSENOT (expression)Condition is FALSE.
(expression) IS UNKNOWNexpression IS NULLExpression evaluates to NULL (same as UNKNOWN).

示例源模式

输入代码：

Redshift
 CREATE TABLE example_data (
    id INT,
    value INT,
    status BOOLEAN,
    category VARCHAR(10)
);

INSERT INTO example_data (id, value, status, category) VALUES
(1, 50, TRUE, 'A'),
(2, 30, FALSE, 'B'),
(3, 40, NULL, 'C'),
(4, 70, TRUE, 'A '),
(5, 60, FALSE, 'B');

SELECT *
FROM example_data
WHERE value < 60 AND value > 40;

SELECT *
FROM example_data
WHERE value <= 60 AND value >= 40;

SELECT *
FROM example_data
WHERE category = 'A';

SELECT *
FROM example_data
WHERE category != 'A' AND category <> 'B';

SELECT *
FROM example_data
WHERE category = ANY(SELECT category FROM example_data WHERE value > 60); --SOME

SELECT *
FROM example_data
WHERE value <> ALL (SELECT value FROM example_data WHERE status = TRUE);

SELECT *
FROM example_data
WHERE status IS TRUE;

SELECT *
FROM example_data
WHERE status IS FALSE;

SELECT *
FROM example_data
WHERE status IS UNKNOWN;
Results

id

值

status

category

1

50

true

A

id

值

status

category

1

50

true

A

3

40

null

C

5

60

false

B

id

值

status

category

1

50

true

A

4

70

true

A

id

值

status

category

3

40

null

C

id

值

status

category

1

50

true

A

4

70

true

A

id

值

status

category

2

30

false

B

4

40

null

C

5

60

false

B

id

值

status

category

1

50

true

A

4

70

true

A

id

值

status

category

2

30

false

B

5

60

false

B

id

值

status

category

4

40

null

C

输出代码：

Snowflake
 CREATE TABLE example_data (
    id INT,
    value INT,
    status BOOLEAN,
    category VARCHAR(10)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}';

INSERT INTO example_data (id, value, status, category) VALUES
(1, 50, TRUE, 'A'),
(2, 30, FALSE, 'B'),
(3, 40, NULL, 'C'),
(4, 70, TRUE, 'A '),
(5, 60, FALSE, 'B');

SELECT *
FROM
    example_data
WHERE value < 60 AND value > 40;

SELECT *
FROM
    example_data
WHERE value <= 60 AND value >= 40;

SELECT *
FROM
    example_data
WHERE category = 'A';

SELECT *
FROM
    example_data
WHERE category != 'A' AND category <> 'B';

SELECT *
FROM
    example_data
WHERE category = ANY(SELECT category FROM
            example_data
        WHERE value > 60); --SOME

SELECT *
FROM
    example_data
WHERE value <> ALL (SELECT value FROM
            example_data
        WHERE status = TRUE);

SELECT *
FROM
    example_data
WHERE status;

SELECT *
FROM
    example_data
WHERE
    NOT status;

SELECT *
FROM
    example_data
WHERE status IS NULL;
Results

id

值

status

category

1

50

true

A

id

值

status

category

1

50

true

A

3

40

null

C

5

60

false

B

id

值

status

category

1

50

true

A

4

70

true

A

id

值

status

category

3

40

null

C

id

值

status

category

1

50

true

A

4

70

true

A

id

值

status

category

2

30

false

B

4

40

null

C

5

60

false

B

id

值

status

category

1

50

true

A

4

70

true

A

id

值

status

category

2

30

false

B

5

60

false

B

id

值

status

category

4

40

null

C

已知问题

未发现任何问题。

相关的 EWIs

没有已知问题。

EXISTS

描述

EXISTS 条件用于测试子查询中是否存在行，如果子查询返回至少一行，则该条件返回 true。如果已指定 NOT，则当子查询没有返回任何行时，条件返回 true。（Redshift SQL 语言参考 EXISTS 条件 (https://docs.aws.amazon.com/redshift/latest/dg/r_exists_condition.html)）

语法

 [ NOT ] EXISTS (table_subquery)

Note

Snowflake 完全支持此函数。

示例源模式

设置表

 CREATE TABLE ExistsTest (
    id INTEGER,
    name VARCHAR(30),
    lastname VARCHAR(30)
);

INSERT INTO ExistsTest (id, name, lastname) VALUES
 (1, 'name1', 'lastname1'),
 (2, 'name2', NULL),
 (3, 'name3', 'lastname3'),
 (4, 'name4', NULL);

 CREATE TABLE ExistsTest (
    id INTEGER,
    name VARCHAR(30),
    lastname VARCHAR(30)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/08/2025",  "domain": "test" }}'

INSERT INTO ExistsTest (id, name, lastname) VALUES
 (1, 'name1', 'lastname1'),
 (2, 'name2', NULL),
 (3, 'name3', 'lastname3'),
 (4, 'name4', NULL);
输入代码：
Redshift
 SELECT * FROM ExistsTest
WHERE EXISTS (
SELECT 1 FROM ExistsTest
WHERE lastname = 'lastname1'
)
ORDER BY id;
Results

ID

NAME

LASTNAME

1

name1

lastname1

2

name2

NULL

3

name3

lastname3

4

name4

NULL
输出代码：
Snowflake
 SELECT * FROM
ExistsTest
WHERE EXISTS (
SELECT 1 FROM
ExistsTest
WHERE lastname = 'lastname1'
)
ORDER BY id;
Results

ID

NAME

LASTNAME

1

name1

lastname1

2

name2

NULL

3

name3

lastname3

4

name4

NULL

相关的 EWIs

无相关的 EWIs。

已知问题

未发现任何问题。

IN

描述

IN 条件用于测试某个值是否属于一个值集合，或者是否包含在某子查询的结果中。（Redshift SQL 语言参考 IN 条件 (https://docs.aws.amazon.com/redshift/latest/dg/r_in_condition.html)）

语法

 expression [ NOT ] IN (expr_list | table_subquery)

Note

Snowflake 完全支持此函数。

示例源模式

设置表

Redshift
 CREATE TABLE sales (
    id INTEGER IDENTITY(1,1),
    price FLOAT,
    saleDate DATE
);

INSERT INTO sales (price, saleDate) VALUES
(5000, '12/19/2024'),
(4000, '12/18/2024'),
(2000, '12/17/2024'),
(1000, '11/11/2024'),
(7000, '10/10/2024'),
(7000, '05/12/2024');

CREATE TABLE InTest (
col1 Varchar(20) COLLATE CASE_INSENSITIVE,
col2 Varchar(30) COLLATE CASE_SENSITIVE,
d1 date,
num integer,
idx integer);

INSERT INTO InTest values ('A', 'A', ('2012-03-02'), 4,6);
INSERT INTO InTest values ('a', 'a', ('2014-01-02'), 41,7);
Snowflake
 CREATE TABLE InTest (
    id INTEGER IDENTITY(1,1) ORDER,
    price FLOAT,
    saleDate DATE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/09/2025",  "domain": "test" }}';

INSERT INTO InTest (price, saleDate) VALUES
(5000, '12/19/2024'),
(4000, '12/18/2024'),
(2000, '12/17/2024'),
(1000, '11/11/2024'),
(7000, '10/10/2024'),
(7000, '05/12/2024');

CREATE TABLE InTest (
col1 Varchar(20) COLLATE 'en-ci',
col2 Varchar(30) COLLATE 'en-cs',
d1 date,
num integer,
idx integer)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/16/2025",  "domain": "test" }}';

INSERT INTO InTest
values ('A', 'A', ('2012-03-02'), 4,6);
INSERT INTO InTest
values ('a', 'a', ('2014-01-02'), 41,7);
输入代码：
Redshift
 SELECT * FROM sales
WHERE id IN (2,3);

SELECT 5 IN (
SELECT id FROM sales
WHERE price = 7000
) AS ValidId;

select t.col1 in ('a ','b','c') as r1, t.col2 in ('a ','b','c') as r2 from InTest t order by t.idx;
Results

ID

PRICE

SALEDATE

2

4000

2024-12-18

3

2000

2024-12-17

VALIDID

TRUE

R1

R2

TRUE

FALSE

TRUE

TRUE
输出代码：
Snowflake
 SELECT * FROM
    sales
WHERE id IN (2,3);

SELECT 5 IN (
SELECT id FROM
 sales
WHERE price = 7000
) AS ValidId;

select t.col1 in (RTRIM('a '), RTRIM('b'), RTRIM('c')) as r1, t.col2 in (RTRIM('a '), RTRIM('b'), RTRIM('c')) as r2 from
InTest t order by t.idx;
Results

ID

PRICE

SALEDATE

2

4000

2024-12-18

3

2000

2024-12-17

VALIDID

TRUE

R1

R2

TRUE

FALSE

TRUE

TRUE

相关的 EWIs

无相关的 EWIs。

已知问题

未发现任何问题。

逻辑条件

描述

逻辑条件将两个条件的结果组合起来，生成一个单一的结果。所有逻辑条件都是具有布尔返回类型的二元运算符。（Redshift SQL 语言参考逻辑条件 (https://docs.aws.amazon.com/redshift/latest/dg/r_logical_condition.html)）。

Note

Snowflake 完全支持此语法。

语法 

expression
{ AND | OR }
expression
NOT expression

E1

E2

E1 AND E2

E1 OR E2

NOT E2

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

FALSE

TRUE

TRUE

TRUE

UNKNOWN

UNKNOWN

TRUE

UNKNOWN

FALSE

TRUE

FALSE

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

UNKNOWN

FALSE

UNKNOWN

UNKNOWN

TRUE

UNKNOWN

TRUE

UNKNOWN

FALSE

FALSE

UNKNOWN

UNKNOWN

UNKNOWN

UNKNOWN

UNKNOWN

示例源模式

设置数据

Redshift
 CREATE TABLE employee (
    employee_id INT,
    active BOOLEAN,
    department VARCHAR(100),
    hire_date DATE,
    salary INT
);

INSERT INTO employee (employee_id, active, department, hire_date, salary) VALUES
    (1, TRUE, 'Engineering', '2021-01-15', 70000),
    (2, FALSE, 'HR', '2020-03-22', 50000),
    (3, NULL, 'Marketing', '2019-05-10', 60000),
    (4, TRUE, 'Engineering', NULL, 65000),
    (5, TRUE, 'Sales', '2018-11-05', NULL);
输入代码：
Redshift
 SELECT
    employee_id,
    (active AND department = 'Engineering') AS is_active_engineering,
    (department = 'HR' OR salary > 60000) AS hr_or_high_salary,
    NOT active AS is_inactive,
    (hire_date IS NULL) AS hire_date_missing,
    (salary IS NULL OR salary < 50000) AS low_salary_or_no_salary
FROM employee;
Results
EMPLOYEE_IDIS_ACTIVE_ENGINEERINGHR_OR_HIGH_SALARYIS_INACTIVEHIRE_DATE_MISSINGLOW_SALARY_OR_NO_SALARY
1TRUETRUEFALSEFALSEFALSE
2FALSETRUETRUEFALSEFALSE
3FALSEFALSENULLFALSEFALSE
4TRUETRUEFALSETRUEFALSE
5FALSENULLFALSEFALSETRUE

输出代码：

Snowflake
 SELECT
    employee_id,
    (active AND department = 'Engineering') AS is_active_engineering,
    (department = 'HR' OR salary > 60000) AS hr_or_high_salary,
    NOT active AS is_inactive,
    (hire_date IS NULL) AS hire_date_missing,
    (salary IS NULL OR salary < 50000) AS low_salary_or_no_salary
FROM
    employee;
Results
EMPLOYEE_IDIS_ACTIVE_ENGINEERINGHR_OR_HIGH_SALARYIS_INACTIVEHIRE_DATE_MISSINGLOW_SALARY_OR_NO_SALARY
1TRUETRUEFALSEFALSEFALSE
2FALSETRUETRUEFALSEFALSE
3FALSEFALSENULLFALSEFALSE
4TRUETRUEFALSETRUEFALSE
5FALSENULLFALSEFALSETRUE

已知问题

未发现任何问题。

相关的 EWIs

没有已知问题。

NULL

描述

NULL 条件用于测试空值，即当某个值缺失或未知时使用。（Redshift SQL 语言参考 NULL 条件 (https://docs.aws.amazon.com/redshift/latest/dg/r_null_condition.html)）

语法

 expression IS [ NOT ] NULL

Note

Snowflake 完全支持此函数。

示例源模式

设置表

Redshift
 CREATE TABLE NullTest (
    id INTEGER,
    name VARCHAR(30),
    lastname VARCHAR(30)
);

INSERT INTO NullTest (id, name, lastname) VALUES 
 (1, 'name1', 'lastname1'),
 (2, 'name2', NULL),
 (3, 'name3', 'lastname3'),
 (4, 'name4', NULL);
Snowflake
 CREATE TABLE NullTest (
    id INTEGER,
    name VARCHAR(30),
    lastname VARCHAR(30)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/08/2025",  "domain": "test" }}';

INSERT INTO NullTest (id, name, lastname) VALUES
 (1, 'name1', 'lastname1'),
 (2, 'name2', NULL),
 (3, 'name3', 'lastname3'),
 (4, 'name4', NULL);
输入代码：
Redshift
 SELECT * FROM nulltest
WHERE lastname IS NULL;
Results

ID

NAME

LASTNAME

2

name2

NULL

4

name4

NULL
输出代码：
Snowflake
 SELECT * FROM
    nulltest
WHERE lastname IS NULL;
Results

ID

NAME

LASTNAME

2

name2

NULL

4

name4

NULL

已知问题

未发现任何问题。

相关的 EWIs

无相关的 EWIs。

模式匹配条件

描述

模式匹配运算符会根据条件表达式中指定的模式，在字符串中搜索匹配项，并根据是否匹配返回 true 或 false。Amazon Redshift 提供三种模式匹配方法：

  • LIKE 表达式 LIKE 运算符将字符串表达式（如列名）与使用通配符 %（百分号）和 _（下划线）表示的模式进行比较。LIKE 模式匹配总是覆盖整个字符串。LIKE 执行区分大小写的的匹配。ILIKE 用于不区分大小写的匹配。

  • SIMILAR TO 正则表达式 SIMILAR TO 运算符将字符串表达式与符合 SQL 标准的正则表达式模式进行匹配，该模式可包含一组模式匹配元字符（包括 LIKE 运算符支持的两种通配符）。SIMILAR TO 匹配整个字符串，并执行区分大小写的匹配。

  • POSIX 风格的正则表达式 POSIX 正则表达式提供比 LIKE 和 SIMILAR TO 运算符更强大的模式匹配能力。POSIX 正则表达式模式可以匹配字符串中的任何部分，并执行区分大小写的匹配。（Redshift SQL 语言参考模式匹配条件 (https://docs.aws.amazon.com/redshift/latest/dg/pattern-matching-conditions.html)）。

已知问题

  • 在 Snowflake 中，当列类型为 CHAR 时，对于 LIKE、SIMILAR TO 和 POSIX 运算符 等场景的行为可能会有所不同。例如：

Code

 CREATE TEMPORARY TABLE pattern_matching_sample (
  col1 CHAR(10),
  col2 VARCHAR(10)
);

INSERT INTO pattern_matching_sample VALUES ('1','1');
INSERT INTO pattern_matching_sample VALUES ('1234567891','1234567891');
INSERT INTO pattern_matching_sample VALUES ('234567891','234567891');

SELECT
col1 LIKE '%1' as "like(CHAR(10))",
COL2 LIKE '%1' as "like(VARCHAR(10))"
FROM
pattern_matching_sample;

Redshift Results

like(CHAR(10))

like(VARCHAR(10))

FALSE

TRUE

TRUE

TRUE

FALSE

TRUE
Snowflake Results

like(CHAR(10))

like(VARCHAR(10))

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

看来，由于 CHAR(10) 是“固定长度”类型，因此它假定“%1”模式必须匹配 CHAR(10) 列第 10 个位置的“1”。但在 Snowflake 中，如果字符串中存在“1”，并且前面有零个或多个字符的任何序列，则成功匹配。

LIKE

模式匹配条件

描述

LIKE 运算符用于将字符串表达式（如列名）与使用通配符 %（百分比）和 _（下划线）的模式进行比较。LIKE 模式匹配始终覆盖整个字符串。要匹配字符串中任何位置的序列，该模式必须以百分号开头和结尾。（Redshift SQL 语言参考 LIKE (https://docs.aws.amazon.com/redshift/latest/dg/r_patternmatching_condition_like.html)）。

Note

This grammar is fully supported in Snowflake.

备注

在 Snowflake 中，如果未提供转义字符，将添加默认 Redshift 转义字符 '\\'，以实现完全等效。

语法 

 expression [ NOT ] LIKE | ILIKE pattern [ ESCAPE 'escape_char' ]

示例源模式

Setup data

Redshift
 CREATE TABLE like_ex(name VARCHAR(20));

INSERT INTO like_ex VALUES
  ('John  Dddoe'),
  ('Joe   Doe'),
  ('Joe   Doe '),
  (' Joe   Doe '),
  (' Joe \n Doe '),
  ('John_down'),
  ('Joe down'),
  ('Elaine'),
  (''),
  (null),
  ('1000 times'),
  ('100%');

Like

输入代码：
Redshift
SELECT name
  FROM like_ex
  WHERE name LIKE '%Jo%oe%'
  ORDER BY name;
Results

NAME

Joe Doe

Joe Doe

Joe Doe

Joe Doe

John Dddoe

输出代码：

Snowflake
 SELECT name
  FROM like_ex
  WHERE name LIKE '%Jo%oe%' ESCAPE '\\'
  ORDER BY name;
Results

NAME

Joe Doe

Joe Doe

Joe Doe

Joe Doe

John Dddoe

Not like

输入代码：
Redshift
 SELECT name
  FROM like_ex
  WHERE name NOT LIKE '%Jo%oe%'
  ORDER BY name;
Results

NAME

100%

1000 times

Elaine

Joe down

John_down

输出代码：

Snowflake
 SELECT name
  FROM like_ex
  WHERE name NOT LIKE '%Jo%oe%' ESCAPE '\\'
  ORDER BY name;
Results

NAME

100%

1000 times

Elaine

Joe down

John_down

转义字符

输入代码：
Redshift
 SELECT name
  FROM like_ex
  WHERE name LIKE '%J%h%^_do%' ESCAPE '^'
  ORDER BY name;
 
SELECT name
 FROM like_ex
 WHERE name LIKE '100\\%'
 ORDER BY 1;
Results

NAME

John_down

NAME

100%

输出代码：

Snowflake
 SELECT name
  FROM like_ex
  WHERE name LIKE '%J%h%^_do%' ESCAPE '^'
  ORDER BY name;
 
SELECT name
 FROM like_ex
 WHERE name LIKE '100\\%' ESCAPE '\\'
 ORDER BY 1;
Results

NAME

John_down

NAME

100%

ILike

输入代码：
Redshift
 SELECT 'abc' LIKE '_B_' AS r1,
       'abc' ILIKE '_B_' AS r2;
Results

R1

R2

FALSE

TRUE

输出代码：

Snowflake
 SELECT 'abc' LIKE '_B_' ESCAPE '\\' AS r1,
       'abc' ILIKE '_B_' ESCAPE '\\' AS r2;
Results

R1

R2

FALSE

TRUE

运算符

以下运算符的转换方式如下：

Redshift

Snowflake

~~

LIKE

!~~

NOT LIKE

~~*

ILIKE

!~~*

NOT ILIKE
输入代码：
Redshift
 SELECT 'abc' ~~ 'abc' AS r1,
       'abc' !~~ 'a%' AS r2,
       'abc' ~~* '_B_' AS r3,
       'abc' !~~* '_B_' AS r4;
Results

R1

R2

R3

R4

TRUE

FALSE

TRUE

FALSE

输出代码：

Snowflake
 SELECT 'abc' LIKE 'abc' ESCAPE '\\' AS r1,
       'abc' NOT LIKE 'a%' ESCAPE '\\' AS r2,
       'abc' ILIKE '_B_' ESCAPE '\\' AS r3,
       'abc' NOT ILIKE '_B_' ESCAPE '\\' AS r4;
Results

R1

R2

R3

R4

TRUE

FALSE

TRUE

FALSE

已知问题

  1. The behavior of fixed char types may differ. See Known issues for more information.

相关的 EWIs

没有已知问题。

POSIX 运算符

模式匹配条件

描述

POSIX 正则表达式是一个用于指定匹配模式的字符序列。如果某个字符串属于正则表达式描述的正则集，则它与该正则表达式相匹配。POSIX 正则表达式模式可匹配字符串的任何部分。（Redshift SQL 语言参考 POSIX 运算符 (https://docs.aws.amazon.com/redshift/latest/dg/pattern-matching-conditions-posix.html)）。

警告

该语法在 Snowflake 中部分受支持。在 Snowflake 中，POSIX 运算符会被转换为 REGEXP_COUNT。

语法 

 expression [ ! ] ~ pattern

POSIX 模式匹配元字符

POSIX 模式匹配支持以下元字符（以下所有示例在 Snowflake 中均受支持）：

POSIX

描述

.

匹配任意单个字符。

*

匹配前一个字符零次或多次。

+

匹配前一个字符一次或多次。

?

匹配前一个字符零次或一次。

|

指定替代匹配项。

^

匹配行首字符。

$

匹配行尾字符。

$

匹配字符串的结尾。

[ ]

方括号指定匹配列表，该列表应匹配列表中的一个表达式。

( )

圆括号将各项分组为单个逻辑项。

{m}

精确重复前一项 m 次。

{m,}

重复前一项 m 或更多次。

{m,n}

重复前一项至少 m 次，但不超过 n 次。

[: :]

匹配 POSIX 字符类中的任何字符。在以下字符类别中，Amazon Redshift 同 Snowflake 一样，都仅支持 ASCII 字符：[:alnum:]、[:alpha:]、[:lower:]、[:upper:]

参数“m”（启用多行模式）和“s”（允许 POSIX 通配符 . 匹配新行）用于在 Snowflake 中实现完全等效的行为。要了解更多信息，请参阅 在 Snowflake 中指定正则表达式的参数。

示例源模式

Setup data

Redshift
 CREATE TABLE posix_test_table (
    id INT,
    column_name VARCHAR(255)
);

INSERT INTO posix_test_table (id, column_name)
VALUES
    (1, 'abc123\nhello world'),
    (2, 'test string\nwith multiple lines\nin this entry'),
    (3, '123abc\nanother line\nabc123'),
    (4, 'line1\nline2\nline3'),
    (5, 'start\nmiddle\nend'),
    (6, 'a@b#c!\nmore text here'),
    (7, 'alpha\nbeta\ngamma'),
    (8, 'uppercase\nlowercase'),
    (9, 'line1\nline2\nline3\nline4'),
    (10, '1234567890\nmore digits'),
    (11, 'abc123\nabc456\nabc789'),
    (12, 'start\nend\nmiddle'),
    (13, 'this is the first line\nthis is the second line'),
    (14, 'special characters\n!@#$%^&*()');

.：匹配任何字符

输入代码：
Redshift
 SELECT id, column_name
FROM posix_test_table
WHERE column_name ~ 'a.c';
Results

ID

COLUMN_NAME

1

abc123 hello world

3

123abc another line abc123

11

abc123 abc456 abc789

输出代码：

Snowflake
 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, 'a.c', 1, 'ms') > 0;
Results

ID

COLUMN_NAME

1

abc123 hello world

3

123abc another line abc123

11

abc123 abc456 abc789

*：匹配前一个字符零次或多次。

输入代码：
Redshift
 SELECT id, column_name
FROM posix_test_table
WHERE column_name ~ 'a*b';
Results

ID

COLUMN_NAME

1

abc123 hello world

3

123abc another line abc123

6

a@b#c! more text here

7

alpha beta gamma

11

abc123 abc456 abc789

输出代码：

Snowflake
 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, 'a*b', 1, 'ms') > 0;
Results

ID

COLUMN_NAME

1

abc123 hello world

3

123abc another line abc123

6

a@b#c! more text here

7

alpha beta gamma

11

abc123 abc456 abc789

?：匹配前一个字符零次或一次

输入代码：
Redshift
 SELECT id, column_name
FROM posix_test_table
WHERE column_name !~ 'a?b';
Results

ID

COLUMN_NAME

2

test string with multiple lines in this entry

4

line1 line2 line3

5

start middle end

8

uppercase lowercase

9

line1 line2 line3 line4

10

1234567890 more digits

12

start end middle

13

this is the first line this is the second line

14

special characters !@#$%^&*()

输出代码：

Snowflake
 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, 'a?b', 1, 'ms') = 0;
Results

ID

COLUMN_NAME

2

test string with multiple lines in this entry

4

line1 line2 line3

5

start middle end

8

uppercase lowercase

9

line1 line2 line3 line4

10

1234567890 more digits

12

start end middle

13

this is the first line this is the second line

14

special characters !@#$%^&*()

^：匹配行首字符

输入代码：
Redshift
 SELECT id, column_name
FROM posix_test_table
WHERE column_name ~ '^abc';
Results

ID

COLUMN_NAME

1

abc123 hello world

3

123abc another line abc123

11

abc123 abc456 abc789

输出代码：

Snowflake
 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, '^abc', 1, 'ms') > 0;
Results

ID

COLUMN_NAME

1

abc123 hello world

3

123abc another line abc123

11

abc123 abc456 abc789

$： 匹配字符串的结尾。

输入代码：
Redshift
 SELECT id, column_name
FROM posix_test_table
WHERE column_name !~ '123$';
Results

ID

COLUMN_NAME

2

test string with multiple lines in this entry

4

line1 line2 line3

5

start middle end

6

a@b#c! more text here

7

alpha beta gamma

8

uppercase lowercase

9

line1 line2 line3 line4

10

1234567890 more digits

12

start end middle

13

this is the first line this is the second line

14

special characters !@#$%^&*()

输出代码：

Snowflake
 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, '123$', 1, 'ms') = 0;
Results

ID

COLUMN_NAME

2

test string with multiple lines in this entry

4

line1 line2 line3

5

start middle end

6

a@b#c! more text here

7

alpha beta gamma

8

uppercase lowercase

9

line1 line2 line3 line4

10

1234567890 more digits

12

start end middle

13

this is the first line this is the second line

14

special characters !@#$%^&*()

使用带有 collate 的列

当前，RLIKE 函数不支持带有 COLLATE 规范的实参。因此，必须禁用 COLLATE 子句才能使用此函数。但是，这可能会导致结果存在差异。

输入代码：
Redshift
 CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE CASE_INSENSITIVE,
col2 VARCHAR(30) COLLATE CASE_SENSITIVE);

INSERT INTO collateTable values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
col1 ~ 'Hello.*' as ci,
col2 ~ 'Hello.*' as cs
FROM collateTable;
Results

CI

CS

TRUE

FALSE

输出代码：

Snowflake
 CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE 'en-ci',
col2 VARCHAR(30) COLLATE 'en-cs'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/16/2025",  "domain": "test" }}';

INSERT INTO collateTable
values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
REGEXP_COUNT(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col1, ''), 'Hello.*', 1, 'ms') > 0 as ci,
REGEXP_COUNT(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col2, ''), 'Hello.*', 1, 'ms') > 0 as cs
FROM
collateTable;
Results

CI

CS

FALSE

FALSE

如果您需要实现这些场景的等效性，可以手动向函数中添加以下参数以实现功能等效：

参数

描述

c

区分大小写的匹配

i

不区分大小写的匹配

已知问题

已知问题

  1. The behavior of fixed char types may differ. See Known issues for more information.

  2. 当前，REGEXP_COUNT 函数不支持带有 COLLATE 规范的实参。

相关的 EWIs

  • SSC-FDM-PG0011：对于此模式匹配条件，已禁用 COLLATE 列约束。

SIMILAR TO

模式匹配条件

描述

SIMILAR TO 运算符用于将字符串表达式（如列名）与 SQL 标准的正则表达式模式进行匹配。SQL 正则表达式模式可以包括一组模式匹配元字符，其中包括 LIKE (https://docs.aws.amazon.com/redshift/latest/dg/r_patternmatching_condition_like.html) 运算符所支持的两个元字符。（Redshift SQL 语言参考 SIMILAR TO (https://docs.aws.amazon.com/redshift/latest/dg/pattern-matching-conditions-similar-to.html)）。

警告

该语法在 Snowflake 中部分受支持。在 Snowflake 中，SIMILAR TO 在转换时会被替换为 RLIKE。

语法 

 expression [ NOT ] SIMILAR TO pattern [ ESCAPE 'escape_char' ]

模式匹配元字符

Redshift SnowflakeNotes
```{code} sql :force: % ``````{code} sql :force: .* ```Matches any sequence of zero or more characters. To achieve full equivalence in Snowflake, we need to replace the '%' operator with '.*' in the pattern.
```{code} sql :force: _ ``````{code} sql :force: . ```Matches any single character. To achieve full equivalence in Snowflake, we need to replace the _ operator with . and add the s parameter to enable the POSIX wildcard character . to match newline characters.
```{code} sql :force: | ``````{code} sql :force: | ```Denotes alternation. This case is fully supported in Snowflake.
```{code} sql :force: * ``````{code} sql :force: * ```Repeat the previous item zero or more times. This can have a different behavior when newline characters are included.
```{code} sql :force: + ``````{code} sql :force: + ```Repeat the previous item one or more times. This can have a different behavior when newline characters are included.
```{code} sql :force: ? ``````{code} sql :force: ? ```Repeat the previous item zero or one time. This can have a different behavior when newline characters are included.
```{code} sql :force: {m} ``````{code} sql :force: {m} ```Repeat the previous item exactly m times and it is fully supported in Snowflake.
```{code} sql :force: {m,} ``````{code} sql :force: {m,} ```Repeat the previous item at least m and not more than n times and it is fully supported in Snowflake.
```{code} sql :force: {m,n} ``````{code} sql :force: {m,n} ```Repeat the previous item m or more times and it is fully supported in Snowflake.
```{code} sql :force: () ``````{code} sql :force: () ```Parentheses group items into a single logical item and it is fully supported in Snowflake.
```{code} sql :force: [...] ``````{code} sql :force: [...] ```A bracket expression specifies a character class, just as in POSIX regular expressions.

示例源模式

Setup data

Redshift
 CREATE TABLE similar_table_ex (
    column_name VARCHAR(255)
);

INSERT INTO similar_table_ex (column_name)
VALUES
    ('abc_123'),
    ('a_cdef'),
    ('bxyz'),
    ('abcc'),
    ('start_hello'),
    ('apple'),
    ('banana'),
    ('xyzabc'),
    ('abc\ncccc'),
    ('\nabccc'),
    ('abc%def'),
    ('abc_xyz'),
    ('abc_1_xyz'),
    ('applepie'),
    ('start%_abc'),
    ('ab%_xyz'),
    ('abcs_123_xyz'),
    ('aabc123'),
    ('xyzxyz'),
    ('123abc\nanother line\nabc123');

% : Matches any sequence of zero or more characters

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO '%abc%';
Results

COLUMN_NAME

abc_123

abcc

xyzabc

abc cccc

abc%def

abc_xyz

abc_1_xyz

start%_abc

abcs_123_xyz

aabc123

输出代码：

Snowflake
 SELECT column_name
FROM similar_table_ex
WHERE RLIKE (column_name, '.*abc.*', 's');
Results

COLUMN_NAME

abc_123

abcc

xyzabc

abc cccc

abc%def

abc_xyz

abc_1_xyz

start%_abc

abcs_123_xyz

aabc123

_：匹配任意单个字符

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'a_c%';
Results

COLUMN_NAME

abc_123

a_cdef

abcc

abc cccc

abc%def

abc_xyz

abc_1_xyz

abcs_123_xyz

输出代码：

Snowflake
 SELECT column_name
FROM similar_table_ex
WHERE RLIKE (column_name, 'a.c.*', 's');
Results

COLUMN_NAME

abc_123

a_cdef

abcc

abc cccc

abc%def

abc_xyz

abc_1_xyz

abcs_123_xyz

|：表示交替

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'a|b%';
Results

COLUMN_NAME

bxyz

banana

输出代码：

Snowflake
 SELECT column_name
FROM similar_table_ex
WHERE RLIKE (column_name, 'a|b.*', 's');
Results

COLUMN_NAME

bxyz

banana

{m, n}：精确重复前一项 m 次。

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'abc{2,4}';
Results

COLUMN_NAME

abcc

输出代码：

Snowflake
 SELECT column_name
FROM similar_table_ex
WHERE RLIKE (column_name, 'abc{2,4}', 's');
Results

COLUMN_NAME

abcc

+： 重复前一项一次或多次

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'abc+';
Results

COLUMN_NAME

abcc

abc cccc

输出代码：

Snowflake
 SELECT column_name
FROM similar_table_ex
WHERE RLIKE (column_name, 'abc+', 's');
Results

COLUMN_NAME

abcc

*：重复前一项零次或多次

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'abc*c';
Results

COLUMN_NAME

abcc

abc cccc

输出代码：

Snowflake
 SELECT column_name
FROM similar_table_ex
WHERE RLIKE (column_name, 'abc*c', 's');
Results

COLUMN_NAME

abcc

?：前一项重复零次或一次

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'abc?c';
Results

COLUMN_NAME

abcc

abc ccc

输出代码：

Snowflake
 SELECT column_name
FROM
similar_table_ex
WHERE
RLIKE( column_name, 'abc?c', 's');
Results

COLUMN_NAME

abcc

()：圆括号将各项分组为单个逻辑项

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO '(abc|xyz)%';
Results

COLUMN_NAME

abc_123

abcc

xyzabc

abc cccc

abc%def

abc_xyz

abc_1_xyz

abcs_123_xyz

xyzxyz

输出代码：

Snowflake
 SELECT column_name
FROM similar_table_ex
WHERE RLIKE (column_name, '(abc|xyz).*', 's');
Results

COLUMN_NAME

abc_123

abcc

xyzabc

abc cccc

abc%def

abc_xyz

abc_1_xyz

abcs_123_xyz

xyzxyz

[...]：指定字符类

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO '[a-c]%';
Results

COLUMN_NAME

abc_123

a_cdef

bxyz

abcc

apple

banana

abc cccc

abc%def

abc_xyz

abc_1_xyz

applepie

ab%_xyz

abcs_123_xyz

aabc123

输出代码：

Snowflake
 SELECT column_name
FROM similar_table_ex
WHERE RLIKE (column_name, '[a-c].*', 's');
Results

COLUMN_NAME

abc_123

a_cdef

bxyz

abcc

apple

banana

abc cccc

abc%def

abc_xyz

abc_1_xyz

applepie

ab%_xyz

abcs_123_xyz

aabc123

转义字符

如果以下字符出现在模式中，且本身不是转义字符，则会被转义：

  • .

  • $

  • ^

输入代码：
Redshift
 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO '%abc^_%' ESCAPE '^';

SELECT '$0.87' SIMILAR TO '$[0-9]+(.[0-9][0-9])?' r1;
Results

COLUMN_NAME

abc_123

abc_xyz

abc_1_xyz

R1

TRUE

输出代码：

Snowflake
 SELECT column_name
FROM
similar_table_ex
WHERE
RLIKE( column_name, '.*abc\\_.*', 's');

SELECT
RLIKE( '$0.87', '\\$[0-9]+(\\.[0-9][0-9])?', 's') r1;
Results

COLUMN_NAME

abc_123

abc_xyz

abc_1_xyz

R1

TRUE

存储在变量中的模式

如果将这些模式存储在变量中，则不会对其应用实现等效性所需的调整。您可参考本文档开头的 表 中所列的建议，以获取其他等效性指南。

输入代码：
Redshift
 WITH pattern AS (
    SELECT '%abc%'::VARCHAR AS search_pattern
)
SELECT column_name
FROM similar_table_ex, pattern
WHERE column_name SIMILAR TO pattern.search_pattern;
Results

COLUMN_NAME

abc_123

abcc

xyzabc

abc cccc

abccc

abc%def

abc_xyz

abc_1_xyz

start%_abc

abcs_123_xyz

aabc123

123abc another line abc123

输出代码：

Snowflake
 WITH pattern AS (
    SELECT '%abc%'::VARCHAR AS search_pattern
)
SELECT column_name
FROM
similar_table_ex,
pattern
WHERE
RLIKE( column_name,
                    --** SSC-FDM-0032 - PARAMETER 'search_pattern' IS NOT A LITERAL VALUE, TRANSFORMATION COULD NOT BE FULLY APPLIED **
                    pattern.search_pattern, 's');
Results

COLUMN_NAME

查询未产生任何结果

使用带有 collate 的列

当前，RLIKE 函数不支持带有 COLLATE 规范的实参。因此，必须禁用 COLLATE 子句才能使用此函数。但是，这可能会导致结果存在差异。

输入代码：
Redshift
 CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE CASE_INSENSITIVE,
col2 VARCHAR(30) COLLATE CASE_SENSITIVE);

INSERT INTO collateTable values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
col1 SIMILAR TO 'Hello%' as ci,
col2 SIMILAR TO 'Hello%' as cs
FROM collateTable;
Results

CI

CS

TRUE

FALSE

输出代码：

Snowflake
 CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE 'en-ci',
col2 VARCHAR(30) COLLATE 'en-cs'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/16/2025",  "domain": "test" }}';

INSERT INTO collateTable
values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
RLIKE(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col1, ''), 'Hello.*', 's') as ci,
RLIKE(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col2, ''), 'Hello.*', 's') as cs
FROM
collateTable;
Results

CI

CS

FALSE

FALSE

如果您需要实现这些场景的等效性，可以手动向函数中添加以下参数以实现功能等效：

参数

描述

c

区分大小写的匹配

i

不区分大小写的匹配

已知问题

  1. 固定字符类型的行为可能有所不同。

  2. RLIKE 函数使用 POSIX 扩展正则表达式，在某些情况下，尤其是在涉及换行符时，这可能会导致不同的行为。 看来，当字符串中存在换行符，并且匹配发生在某一行时，即使该匹配仅发生在该行而非整个字符串上，它也会返回整个字符串匹配成功的结果。例如：

Redshift code

 CREATE TABLE table1 (
col1 VARCHAR(20)
);

INSERT INTO table1 values ('abcccc'), ('abc\neab'), ('abc\nccc');

SELECT col1
FROM table1
WHERE col1 SIMILAR TO 'abc*c';
Snowflake code
 CREATE TABLE table1 (
col1 VARCHAR(20)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/14/2025",  "domain": "test" }}';

INSERT INTO table1
values ('abcccc'), ('abc\neab'), ('abc\nccc');

SELECT col1
FROM
table1
WHERE
RLIKE( col1, 'abc*c', 's');
Redshift Results

COL1

abcccc

abc eab

abc ccc
Snowflake Results

COL1

abcccc

  1. To achieve maximum equivalence, some modifications are made to the pattern operators.

  2. If these patterns are stored in a variable, SnowConvert AI does not apply the necessary adjustments for equivalence.

  3. 当前，RLIKE 函数不支持带有 COLLATE 规范的实参。

相关的 EWIs

  • SSC-FDM-0032：参数不是字面量值，未完全应用转换。

  • SSC-FDM-PG0011：对于此模式匹配条件，已禁用 COLLATE 列约束。