SnowConvert AI - Redshift - Conditions

BETWEEN

Description

A BETWEEN condition tests expressions for inclusion in a range of values, using the keywords BETWEEN and AND. (Redshift SQL Language Reference BETWEEN condition)

Grammar Syntax


 expression [ NOT ] BETWEEN expression AND expression

Note

:class: tip This function is fully supported by Snowflake.

Sample Source Patterns

Setup Table

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');
Input Code:
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
idpricedepartmentidsaledate
idpricedepartmentidsaledate
1500012008-01-01
2800012018-01-01
3500022010-01-01
4700032010-01-01
5500012018-01-01
6400042010-01-01
7300042018-01-01
8900052008-01-01
9700052018-01-01
10600052006-01-01
11500052008-01-01
12500042018-01-01
13800032006-01-01
14700032016-01-01
15200022018-01-01
idpricedepartmentidsaledate
3500022010-01-01
4700032010-01-01
6400042010-01-01
14700032016-01-01
Output Code:
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
idpricedepartmentidsaledate
idpricedepartmentidsaledate
1500012008-01-01
2800012018-01-01
3500022010-01-01
4700032010-01-01
5500012018-01-01
6400042010-01-01
7300042018-01-01
8900052008-01-01
9700052018-01-01
10600052006-01-01
11500052008-01-01
12500042018-01-01
13800032006-01-01
14700032016-01-01
15200022018-01-01
idpricedepartmentidsaledate
3500022010-01-01
4700032010-01-01
6400042010-01-01
14700032016-01-01

Known Issues

No issues were found.

No related EWIs.

Comparison Condition

Conditions

Description

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)

Grammar Syntax

Redshift supports the comparison operators described in the following table:

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.

Use of comparison operators on Strings

It is important to note that in Redshift, comparison operators on strings ignore trailing blank spaces. To replicate this behavior in Snowflake, the transformation applies the RTRIM function to remove trailing spaces, ensuring equivalent functionality. For more information: Significance of trailing blanks

Conversion Table

Most of the operators are directly supported by Snowflake; however, the following operators require transformation:

RedshiftSnowflakeComments
(expression) IS TRUEexpression

Condition is TRUE.

(expression) IS FALSENOT (expression)

Condition is FALSE.

(expression) IS UNKNOWNexpression IS NULL

Expression evaluates to NULL (same as UNKNOWN ).

Sample Source Patterns

Input Code:

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
idvaluestatuscategory
150trueA
idvaluestatuscategory
150trueA
340nullC
560falseB
idvaluestatuscategory
150trueA
470trueA
idvaluestatuscategory
340nullC
idvaluestatuscategory
150trueA
470trueA
idvaluestatuscategory
230falseB
440nullC
560falseB
idvaluestatuscategory
150trueA
470trueA
idvaluestatuscategory
230falseB
560falseB
idvaluestatuscategory
440nullC

Output Code:

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
idvaluestatuscategory
150trueA
idvaluestatuscategory
150trueA
340nullC
560falseB
idvaluestatuscategory
150trueA
470trueA
idvaluestatuscategory
340nullC
idvaluestatuscategory
150trueA
470trueA
idvaluestatuscategory
230falseB
440nullC
560falseB
idvaluestatuscategory
150trueA
470trueA
idvaluestatuscategory
230falseB
560falseB
idvaluestatuscategory
440nullC

Known Issues

No issues were found.

There are no known issues.

EXISTS

Description

EXISTS conditions test for the existence of rows in a subquery, and return true if a subquery returns at least one row. If NOT is specified, the condition returns true if a subquery returns no rows. (Redshift SQL Language Reference EXISTS condition)

Grammar Syntax


 [ NOT ] EXISTS (table_subquery)

Note

:class: tip This function is fully supported by Snowflake.

Sample Source Patterns

Setup Table


 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);
Input Code:
Redshift

 SELECT * FROM ExistsTest
WHERE EXISTS (
SELECT 1 FROM ExistsTest
WHERE lastname = 'lastname1'
)
ORDER BY id;
Results
IDNAMELASTNAME
1name1lastname1
2name2NULL
3name3lastname3
4name4NULL
Output Code:
Snowflake

 SELECT * FROM
ExistsTest
WHERE EXISTS (
SELECT 1 FROM
ExistsTest
WHERE lastname = 'lastname1'
)
ORDER BY id;
Results
IDNAMELASTNAME
1name1lastname1
2name2NULL
3name3lastname3
4name4NULL

No related EWIs.

Known Issues

No issues were found.

IN

Description

An IN condition tests a value for membership in a set of values or in a subquery. (Redshift SQL Language Reference IN condition)

Grammar Syntax


 expression [ NOT ] IN (expr_list | table_subquery)

Note

:class: tip This function is fully supported by Snowflake.

Sample Source Patterns

Setup Table

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);
Input Code:
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
IDPRICESALEDATE
240002024-12-18
320002024-12-17
VALIDID
TRUE
R1R2
TRUEFALSE
TRUETRUE
Output Code:
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
IDPRICESALEDATE
240002024-12-18
320002024-12-17
VALIDID
TRUE
R1R2
TRUEFALSE
TRUETRUE

No related EWIs.

Known Issues

No issues were found.

Logical Conditions

Description

Logical conditions combine the result of two conditions to produce a single result. All logical conditions are binary operators with a Boolean return type. (Redshift SQL Language reference Logical Conditions).

Note

:class: tip This grammar is fully supported in Snowflake.

Grammar Syntax 

:force:
expression
{ AND | OR }
expression
NOT expression
E1E2E1 AND E2E1 OR E2NOT E2
TRUETRUETRUETRUEFALSE
TRUEFALSEFALSETRUETRUE
TRUEUNKNOWNUNKNOWNTRUEUNKNOWN
FALSETRUEFALSETRUE
FALSEFALSEFALSEFALSE
FALSEUNKNOWNFALSEUNKNOWN
UNKNOWNTRUEUNKNOWNTRUE
UNKNOWNFALSEFALSEUNKNOWN
UNKNOWNUNKNOWNUNKNOWNUNKNOWN

Sample Source Patterns

Setup data

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);
Input Code:
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

Output Code:

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

Known Issues

No issues were found.

There are no known issues.

NULL

Description

The null condition tests for nulls, when a value is missing or unknown. (Redshift SQL Language Reference NULL condition)

Grammar Syntax


 expression IS [ NOT ] NULL

Note

:class: tip This function is fully supported by Snowflake.

Sample Source Patterns

Setup Table

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);
Input Code:
Redshift

 SELECT * FROM nulltest
WHERE lastname IS NULL;
Results
IDNAMELASTNAME
2name2NULL
4name4NULL
Output Code:
Snowflake

 SELECT * FROM
    nulltest
WHERE lastname IS NULL;
Results
IDNAMELASTNAME
2name2NULL
4name4NULL

Known Issues

No issues were found.

No related EWIs.

Pattern-matching conditions

Description

A pattern-matching operator searches a string for a pattern specified in the conditional expression and returns true or false depend on whether it finds a match. Amazon Redshift uses three methods for pattern matching:

  • LIKE expressions The LIKE operator compares a string expression, such as a column name, with a pattern that uses the wildcard characters % (percent) and _ (underscore). LIKE pattern matching always covers the entire string. LIKE performs a case-sensitive match and ILIKE performs a case-insensitive match.
  • SIMILAR TO regular expressions The SIMILAR TO operator matches a string expression with a SQL standard regular expression pattern, which can include a set of pattern-matching metacharacters that includes the two supported by the LIKE operator. SIMILAR TO matches the entire string and performs a case-sensitive match.
  • POSIX-style regular expressions POSIX regular expressions provide a more powerful means for pattern matching than the LIKE and SIMILAR TO operators. POSIX regular expression patterns can match any portion of the string and performs a case-sensitive match. (Redshift SQL Language reference Pattern-matching conditions).

Known Issues

  • In Snowflake, the behavior for scenarios such as (LIKE, SIMILAR TO, and POSIX Operators) can vary when the column is of type CHAR. For example:

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))
FALSETRUE
TRUETRUE
FALSETRUE
Snowflake Results
like(CHAR(10))like(VARCHAR(10))
TRUETRUE
TRUETRUE
TRUETRUE

It appears that, because CHAR(10) is “fixed-length,” it assumes the ‘%1’ pattern must match a ’1’ in the 10th position of a CHAR(10) column. However, in Snowflake, it matches if a ’1’ exists in the string, with any sequence of zero or more characters preceding it.

LIKE

Pattern-matching conditions

Description

The LIKE operator compares a string expression, such as a column name, with a pattern that uses the wildcard characters % (percent) and _ (underscore). LIKE pattern matching always covers the entire string. To match a sequence anywhere within a string, the pattern must start and end with a percent sign. (Redshift SQL Language reference LIKE).

Note

:class: tip This grammar is fully supported in Snowflake.

Note

In Snowflake the cases where the escape character is not provided, the default Redshift escape character '\\' will be added for full equivalence.

Grammar Syntax 


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

Sample Source Patterns

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

Input Code:
Redshift
:force:
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

Output Code:

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

Input Code:
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

Output Code:

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

Escape characters

Input Code:
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%

Output Code:

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

Input Code:
Redshift

 SELECT 'abc' LIKE '_B_' AS r1,
       'abc' ILIKE '_B_' AS r2;
Results
R1R2
FALSETRUE

Output Code:

Snowflake

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

Operators

The following operators are translated as follows:

RedshiftSnowflake
~~LIKE
!~~NOT LIKE
~~*ILIKE
!~~*NOT ILIKE
Input Code:
Redshift

 SELECT 'abc' ~~ 'abc' AS r1,
       'abc' !~~ 'a%' AS r2,
       'abc' ~~* '_B_' AS r3,
       'abc' !~~* '_B_' AS r4;
Results
R1R2R3R4
TRUEFALSETRUEFALSE

Output Code:

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
R1R2R3R4
TRUEFALSETRUEFALSE

Known Issues

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

There are no known issues.

POSIX Operators

Pattern-matching conditions

Description

A POSIX regular expression is a sequence of characters that specifies a match pattern. A string matches a regular expression if it is a member of the regular set described by the regular expression. POSIX regular expression patterns can match any portion of a string. (Redshift SQL Language reference POSIX Operators).

Warning

This grammar is partially supported in Snowflake. POSIX Operators are transformed to REGEXP_COUNT in Snowflake.

Grammar Syntax 


 expression [ ! ] ~ pattern

POSIX pattern-matching metacharacters

POSIX pattern matching supports the following metacharacters (all the cases are supported in Snowflake):

POSIXDescription
.Matches any single character.
*Matches zero or more occurrences.
+Matches one or more occurrences.
?Matches zero or one occurrence.
|Specifies alternative matches.
^Matches the beginning-of-line character.
$Matches the end-of-line character.
$Matches the end of the string.
[ ]Brackets specify a matching list, that should match one expression in the list.
( )Parentheses group items into a single logical item.
{m}Repeat the previous item exactly m times.
{m,}Repeat the previous item m or more times.
{m,n}Repeat the previous item at least m and not more than n times.
[: :]Matches any character within a POSIX character class. In the following character classes, Amazon Redshift supports only ASCII characters, just like Snowflake: [:alnum:], [:alpha:], [:lower:], [:upper:]

The parameters ‘m’ (enables multiline mode) and ‘s’ (allows the POSIX wildcard character . to match new lines) are used to achieve full equivalence in Snowflake. For more information please refer to Specifying the parameters for the regular expression in Snowflake.

Sample Source Patterns

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!@#$%^&*()');

. : Matches any character

Input Code:
Redshift

 SELECT id, column_name
FROM posix_test_table
WHERE column_name ~ 'a.c';
Results
IDCOLUMN_NAME
1abc123 hello world
3123abc another line abc123
11abc123 abc456 abc789

Output Code:

Snowflake

 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, 'a.c', 1, 'ms') > 0;
Results
IDCOLUMN_NAME
1abc123 hello world
3123abc another line abc123
11abc123 abc456 abc789

* : Matches zero or more occurrences.

Input Code:
Redshift

 SELECT id, column_name
FROM posix_test_table
WHERE column_name ~ 'a*b';
Results
IDCOLUMN_NAME
1abc123 hello world
3123abc another line abc123
6a@b#c! more text here
7alpha beta gamma
11abc123 abc456 abc789

Output Code:

Snowflake

 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, 'a*b', 1, 'ms') > 0;
Results
IDCOLUMN_NAME
1abc123 hello world
3123abc another line abc123
6a@b#c! more text here
7alpha beta gamma
11abc123 abc456 abc789

? : Matches zero or one occurrence

Input Code:
Redshift

 SELECT id, column_name
FROM posix_test_table
WHERE column_name !~ 'a?b';
Results
IDCOLUMN_NAME
2test string with multiple lines in this entry
4line1 line2 line3
5start middle end
8uppercase lowercase
9line1 line2 line3 line4
101234567890 more digits
12start end middle
13this is the first line this is the second line
14special characters !@#$%^&*()

Output Code:

Snowflake

 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, 'a?b', 1, 'ms') = 0;
Results
IDCOLUMN_NAME
2test string with multiple lines in this entry
4line1 line2 line3
5start middle end
8uppercase lowercase
9line1 line2 line3 line4
101234567890 more digits
12start end middle
13this is the first line this is the second line
14special characters !@#$%^&*()

^ : Matches the beginning-of-line character

Input Code:
Redshift

 SELECT id, column_name
FROM posix_test_table
WHERE column_name ~ '^abc';
Results
IDCOLUMN_NAME
1abc123 hello world
3123abc another line abc123
11abc123 abc456 abc789

Output Code:

Snowflake

 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, '^abc', 1, 'ms') > 0;
Results
IDCOLUMN_NAME
1abc123 hello world
3123abc another line abc123
11abc123 abc456 abc789

$ : Matches the end of the string.

Input Code:
Redshift

 SELECT id, column_name
FROM posix_test_table
WHERE column_name !~ '123$';
Results
IDCOLUMN_NAME
2test string with multiple lines in this entry
4line1 line2 line3
5start middle end
6a@b#c! more text here
7alpha beta gamma
8uppercase lowercase
9line1 line2 line3 line4
101234567890 more digits
12start end middle
13this is the first line this is the second line
14special characters !@#$%^&*()

Output Code:

Snowflake

 SELECT id, column_name
FROM posix_test_table
WHERE REGEXP_COUNT(column_name, '123$', 1, 'ms') = 0;
Results
IDCOLUMN_NAME
2test string with multiple lines in this entry
4line1 line2 line3
5start middle end
6a@b#c! more text here
7alpha beta gamma
8uppercase lowercase
9line1 line2 line3 line4
101234567890 more digits
12start end middle
13this is the first line this is the second line
14special characters !@#$%^&*()

Usage of collate columns

Arguments with COLLATE specifications are not currently supported in the RLIKE function. As a result, the COLLATE clause must be disabled to use this function. However, this may lead to differences in the results.

Input Code:
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
CICS
TRUEFALSE

Output Code:

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
CICS
FALSEFALSE

If you require equivalence for these scenarios, you can manually add the following parameters to the function to achieve functional equivalence:

ParameterDescription
cCase-sensitive matching
iCase-insensitive matching

Known Issues

Known Issues

  1. The behavior of fixed char types may differ. See Known issues for more information.
  2. Arguments with COLLATE specifications are not currently supported in the REGEXP_COUNT function.
  • SSC-FDM-PG0011: The use of the COLLATE column constraint has been disabled for this pattern-matching condition.

SIMILAR TO

Pattern-matching conditions

Description

The SIMILAR TO operator matches a string expression, such as a column name, with a SQL standard regular expression pattern. A SQL regular expression pattern can include a set of pattern-matching metacharacters, including the two supported by the LIKE operator. (Redshift SQL Language reference SIMILAR TO).

Warning

This grammar is partially supported in Snowflake. SIMILAR TO is transformed to RLIKE in Snowflake.

Grammar Syntax 


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

Pattern-matching metacharacters

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.

Sample Source Patterns

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

Input Code:
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

Output Code:

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

_ : Matches any single character

Input Code:
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

Output Code:

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

| : Denotes alternation

Input Code:
Redshift

 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'a|b%';
Results
COLUMN_NAME
bxyz
banana

Output Code:

Snowflake

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

{m, n} : Repeat the previous item exactly m times.

Input Code:
Redshift

 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'abc{2,4}';
Results
COLUMN_NAME
abcc

Output Code:

Snowflake

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

+ : Repeat the previous item one or more times

Input Code:
Redshift

 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'abc+';
Results
COLUMN_NAME
abcc
abc cccc

Output Code:

Snowflake

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

* : Repeat the previous item zero or more times

Input Code:
Redshift

 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'abc*c';
Results
COLUMN_NAME
abcc
abc cccc

Output Code:

Snowflake

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

? : Repeat the previous item zero or one time

Input Code:
Redshift

 SELECT column_name
FROM similar_table_ex
WHERE column_name SIMILAR TO 'abc?c';
Results
COLUMN_NAME
abcc
abc ccc

Output Code:

Snowflake

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

() : Parentheses group items into a single logical item

Input Code:
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

Output Code:

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

[…] : Specifies a character class

Input Code:
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

Output Code:

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

Escape characters

The following characters will be escaped if they appear in the pattern and are not the escape character itself:

  • .
  • $
  • ^
Input Code:
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

Output Code:

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

Pattern stored in a variable

If these patterns are stored in a variable, the required adjustments for equivalence will not be applied. You can refer to the recommendations outlined in the table at the beginning of this document for additional equivalence guidelines.

Input Code:
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

Output Code:

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
Query produced no results

Usage of collate columns

Arguments with COLLATE specifications are not currently supported in the RLIKE function. As a result, the COLLATE clause must be disabled to use this function. However, this may lead to differences in the results.

Input Code:
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
CICS
TRUEFALSE

Output Code:

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
CICS
FALSEFALSE

If you require equivalence for these scenarios, you can manually add the following parameters to the function to achieve functional equivalence:

ParameterDescription
cCase-sensitive matching
iCase-insensitive matching

Known Issues

  1. The behavior of fixed char types may differ.
  2. The RLIKE function uses POSIX extended regular expressions, which may result in different behavior in certain cases, especially when line breaks are involved. It appears that when line breaks are present in the string and a match occurs on one line, it returns a positive result for the entire string, even though the match only occurred on a single line and not across the whole string. For example:

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. Arguments with COLLATE specifications are not currently supported in the RLIKE function.
  • SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied.
  • SSC-FDM-PG0011: The use of the COLLATE column constraint has been disabled for this pattern-matching condition.