LIKE

LIKE Predicate

Description

Retrieves rows where a string expression—typically a column—matches the specified pattern or, if qualified by ANY or ALL, set of patterns (Vertica SQL Language Reference Like Predicate)

Grammar Syntax

string-expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB }
   { pattern | { ANY | SOME | ALL } ( pattern,... ) } [ ESCAPE 'char' ]

Vertica Substitute symbols

Symbol	Vertica Equivalent	Snowflake Equivalent
~~	LIKE	LIKE
~#	LIKEB	LIKE
~~*	ILIKE	ILIKE
~#*	ILIKEB	ILIKE
!~~	NOT LIKE	NOT LIKE
!~#	NOT LIKEB	NOT LIKE
!~~*	NOT ILIKE	NOT ILIKE
!~#*	NOT ILIKEB	NOT ILIKE

In Vertica, the default escape character is the backslash (\). Snowflake doesn't have a default escape character. SnowConvert will automatically add the ESCAPE clause when needed.

It's important to know that Snowflake requires the backslash to be escaped (\\) when you use it as an escape character within both the expression and the ESCAPE clause. This means you'll need two backslashes to represent a single literal backslash escape character in Snowflake queries. SnowConvert handles this by automatically escaping the backslash for you.

Sample Source Patterns

This syntax is fully supported in Snowflake.

Input Code:

Vertica

IN -> Vertica_01.sql

SELECT path_name
FROM file_paths
WHERE path_name ~~ '/report/sales_2025_q_.csv';

-- Find a path containing the literal '50%'
SELECT path_name
FROM file_paths
WHERE path_name LIKE '%50\%%';

-- Find a path starting with 'C:\'
SELECT path_name
FROM file_paths
WHERE path_name ILIKEB 'C:\\%' ESCAPE'\';

Output Code:

Snowflake

OUT -> Vertica_01.sql

ELECT path_name
FROM file_paths
WHERE path_name LIKE '/report/sales_2025_q_.csv';

-- Find a path containing the literal '50%'
SELECT path_name
FROM file_paths
WHERE path_name LIKE '%50\\%%' ESCAPE'\\';

-- Find a path starting with 'C:\'
SELECT path_name
FROM file_paths
WHERE path_name ILIKE 'C:\\\\%' ESCAPE'\\';

Known Issues

While SnowConvert handles most backslash patterns, some complex expressions may still cause query failures. We recommend reviewing complex patterns to prevent these issues.

Related EWIs

There are no related EWIs.