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))
FALSE	TRUE
TRUE	TRUE
FALSE	TRUE

Snowflake Results

like(CHAR(10))	like(VARCHAR(10))
TRUE	TRUE
TRUE	TRUE
TRUE	TRUE

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.