REGEXP_LIKE

Evaluates a regular expression pattern and determines if it is contained within the given string.

This function is similar to the LIKE operator, except that the pattern only needs to be contained within the string, rather than needing to match all of the string. In other words, this performs a contains operation rather than a match operation. You can match the entire string by anchoring the pattern using ^ and $.

Syntax

REGEXP_LIKE(STRING, PATTERN)

Arguments

STRING

Type: string

A sequence of characters.

PATTERN

Type: string

A regular expression pattern.

This pattern must be a Java regular expression. String literals are unescaped. For example, to match '\abc', a regular expression would be '^\\abc$'.

See the RegEx pattern table for more information.

Returns

Type: Boolean

A Boolean value indicating whether or not PATTERN is contained with STRING.

Examples

STRING	PATTERN	Output
'1a 2b 14m'	'\d+b'	true
'1a 2b 14m'	'^1'	true
'ab abc abcc bac'	'ab*'	true
'ab a1bc ab1cc bac'	'a1*'	true
'2 a1bc ab11cc 311'	'a1(2)'	true
'2 a1bc ab11cc 311'	'a1(2)sdag'	false
null	'\d+'	null

Transformation job example

SQL

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_address_city        
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE ($commit_time BETWEEN run_start_time() AND run_end_time())
    AND REGEXP_LIKE(customer_address_city, '(.*ville$)|(.*burg$)')
    LIMIT 3;

Query result

customer_address_city
Sykesville
Collierville
Jeffersonville