LIKE

LIKE

The LIKE operator is used to match a specified character pattern in a string.

These patterns can contain regular characters as well as wildcards. Wildcard characters can be escaped using the single character specified for the ESCAPE parameter.

Note that matching is case sensitive. To do case insensitive matching, you can first apply either the LOWER or UPPER function to the expression you are matching.

Syntax

<expression> LIKE '<pattern>' [ ESCAPE '<escape_character>' ]

Note that if pattern or escape_character is null, then the expression evaluates to null.

Wildcards

Wildcard	Description
%	Represents zero, one, or multiple characters
_	Represents a single character

Escape character

The escape_character can be any character that you define that is used to escape one of the wildcard characters when you want to match the literal character itself.

For example, if you wanted to match the percent sign % within expression and not use it as a wildcard, you could do LIKE '#%' ESCAPE '#'. Using # as the escape character means that when it's put in front of a wildcard character such as %, it matches the exact character % instead of representing zero, one, or multiple characters the way it typically does.

Examples

Transformation job with sample data

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true	
    AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
    SELECT 
        customer_email,
        tax_percent
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    LET tax_percent = TRUNCATE(taxrate * 100) || '%'
    WHERE (time_filter())
    AND customer_email LIKE '[e-mail]'
    AND tax_percent LIKE '12%#%' ESCAPE '#'
    LIMIT 3;

Query result

customer_email	tax_percent
[e-mail]	12.0%
[e-mail]	12.0%
[e-mail]	12.0%