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).

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):

POSIX	Description
.	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

IN -> Redshift_01.sql

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

Output Code:

Snowflake

OUT -> Redshift_01.sql

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

* : Matches zero or more occurrences.

Input Code:

Redshift

IN -> Redshift_02.sql

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

Output Code:

Snowflake

OUT -> Redshift_02.sql

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

? : Matches zero or one occurrence

Input Code:

Redshift

IN -> Redshift_03.sql

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

Output Code:

Snowflake

OUT -> Redshift_03.sql

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

^ : Matches the beginning-of-line character

Input Code:

Redshift

IN -> Redshift_04.sql

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

Output Code:

Snowflake

OUT -> Redshift_04.sql

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

$ : Matches the end of the string.

Input Code:

Redshift

IN -> Redshift_05.sql

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

Output Code:

Snowflake

OUT -> Redshift_05.sql

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

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

IN -> Redshift_06.sql

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

Output Code:

Snowflake

OUT -> Redshift_12.sql

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

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

Parameter	Description
c	Case-sensitive matching
i	Case-insensitive matching

Known Issues

Known Issues

1. The behavior of fixed char types may differ. Click here for more information.
2. Arguments with COLLATE specifications are not currently supported in the REGEXP_COUNT function.

Related EWIs

• SSC-FDM-PG0011: The use of the COLLATE column constraint has been disabled for this pattern-matching condition.