POSIX Operators

Pattern-matching conditions

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

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

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

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

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

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

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

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

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

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

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

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

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.