LIKE

LIKE Predicate

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

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.