Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

LIKE

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6

Description

The LIKE predicate determines whether the right expression string matches the left expression string. The output can be negated by using NOT LIKE.

LIKE can only be applied to STRING values.

[NOT] LIKE can be used in the following contexts:

Syntax
table.column LIKE pattern
table.column NOT LIKE pattern
Wildcards

The following wildcards can be used in the right pattern expression:

  • %: Matches any string with zero or more characters.

  • _: Matches exactly one character.

Wildcards can be escaped with a backslash (\%, \_). If no wildcards are contained, leading and trailing % wildcards are assumed, and comparison is not case sensitive.

Examples

[1] No wildcards are included. LIKE will assume leading and trailing '%' wildcards, which means that LIKE matches in all examples.

Query

Column1

CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Haystack : STRING

Needle : STRING

'abcde'

'a'

'abcde'

'bcd'

'abcde'

'abcde'

'abcde'

'e'

'aBcDE'

'e'

Result

Column1 : STRING

'match'

'match'

'match'

'match'

'match'

[2] Wildcards are included. LIKE will not leading and trailing '%' wildcards, which means that LIKE does not match in all examples.

Query

Column1

CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Haystack : STRING

Needle : STRING

'abcde'

'a_'

'abcde'

'b_d'

'abcde'

'a%d'

'abcde'

'_e'

Result

Column1 : STRING

'no match'

'no match'

'no match'

'no match'

[3] Wildcards are included. LIKE will not leading and trailing '%' wildcards, but in all examples, LIKE matches.

Input

Output

Table1

Haystack : STRING

Needle : STRING

'abcde'

'a_c_e'

'abcde'

'_b_d_'

'abcde'

'a%d_'

'abcde'

'%e%'

Result

Column1 : STRING

'match'

'match'

'match'

'match'

[4] If no wildcards are included, comparison is case insensitive. If wildcards are included, comparison is case sensitive.

Query

Column1

CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Haystack : STRING

Needle : STRING

'abcde'

'a'

'abcde'

'A'

'abcde'

'aBcDe'

'abcde'

'B'

'abcde'

'a_C_e'

'abcde'

'_B%'

'abcde'

'%e%'

'abcde'

'%E%'

'aBcDe'

'AbCdE'

Result

Column1 : STRING

'match'

'match'

'match'

'match'

'no match'

'no match'

'match'

'no match'

'match'

[5] NOT LIKE: Results are negated. If no wildcards are included, comparison is case insensitive. If wildcards are included, comparison is case sensitive.

Query

Column1

CASE WHEN ( "Table1"."Haystack" NOT LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Haystack : STRING

Needle : STRING

'abcde'

'a'

'abcde'

'A'

'abcde'

'aBcDe'

'abcde'

'B'

'abcde'

'a_C_e'

'abcde'

'_B%'

'abcde'

'%e%'

'abcde'

'%E%'

'aBcDe'

'AbCdE'

Result

Column1 : STRING

'no match'

'no match'

'no match'

'no match'

'match'

'match'

'no match'

'match'

'no match'

[6] If any side of the LIKE operator is NULL, NULL is returned.

Query

Column1

CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Needle : STRING

Haystack : STRING

null

null

null

''

null

''

null

''

null

''

null

'0'

null

'%'

null

'_'

'abc'

null

Result

Column1 : STRING

null

null

null

null

null

null

null

null

null

[7] Wildcards can be escaped with a backslash.

Query

Column1

CASE WHEN ( "Table1"."Haystack" LIKE "Table1"."Needle" ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Haystack : STRING

Needle : STRING

'_a'

'\_a'

'%a'

'\%a'

'ab'

'\_b'

'ab'

'\%b'

'a'

'\_'

'aba'

'\%'

Result

Column1 : STRING

'match'

'match'

'no match'

'no match'

'no match'

'no match'

[8] The expression strings can also be string literals.

Query

Column1

CASE WHEN ( "Table1"."Haystack" LIKE 'a' ) THEN 'match' ELSE 'no match' END

Input

Output

Table1

Haystack : STRING

'a'

'a a'

'aba'

''

''

null

Result

Column1 : STRING

'match'

'match'

'match'

'no match'

'no match'

null

[9] Use LIKE in a FILTER expression.

Query

Filter

FILTER "Table1"."Haystack" LIKE "Table1"."Needle";

Column1

"Table1"."Haystack"

Column2

"Table1"."Needle"

Input

Output

Table1

Haystack : STRING

Needle : STRING

'abcde'

'a'

'abcde'

'bcd'

'abcde'

'abcde'

'abcde'

'%e'

'abcde'

'E'

'abcde'

'%E'

'abcde'

'_b'

null

'a'

'abcde'

null

'%a'

'\%a'

'ab'

'\_b'

Result

Column1 : STRING

Column2 : STRING

'abcde'

'a'

'abcde'

'bcd'

'abcde'

'abcde'

'abcde'

'%e'

'abcde'

'E'

'%a'

'\%a'