Pattern Matching

Extreme DPS allows you to use the LIKE operator and regular expressions to implement pattern matching.

---

LIKE

Checks whether a string matches the specified pattern. LIKE is case sensitive.

Note that LIKE does not support nondeterministic collations. To work around this limitation, you can apply a different collation to the expression.

Syntax

<string> LIKE <pattern> [ESCAPE <escape-character>]
<string> NOT LIKE <pattern> [ESCAPE <escape-character>]

Arguments

• <string>

  The string to match the pattern. It can be varchar or text.

• <pattern>

  The pattern to match. It can be varchar or text.

• <escape-character>

  The one or more characters inserted in front of a wildcard character to indicate that the wildcard must be interpreted as a regular character not a a wildcard.

Returns

A boolean value, or null if <string> or <pattern> is null.

Examples

LIKE operator:

'john' LIKE 'john' → true
'john' LIKE '%o%' → true
'john' LIKE '_oh_' → true
'john' LIKE 'tom' → false
'john' LIKE NULL → NULL
NULL LIKE '%abc%' → NULL

The output can be:

• true, when <string> matches <pattern>.

• false, when <string> does not match <pattern>.

• NULL, when <string> or <pattern> is null.

NOT LIKE operator:

'john' NOT LIKE 'john' → false
'john' NOT LIKE '%o%' → false
'john' NOT LIKE '_oh_' → false
'john' NOT LIKE 'tom' → true
'john' NOT LIKE NULL → NULL
NULL NOT LIKE '%abc%' → NULL

The output can be:

• true, when <string> does not match <pattern>.

• false, when <string> matches <pattern>.

• NULL, when <string> or <pattern> is null.

Usage notes

The LIKE pattern matching always covers the entire string. Therefore, if you want to match a sequence within a string, start and end the pattern with a percent sign (%).

To match a literal underscore (_) or percent sign (%) without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash (). You can use the ESCAPE clause to specify one that suits your need. To match the escape character itself, write two escape characters consecutively.

note

If you disable standard_conforming_strings, you need to double quote the backslashes () in literal string constants.

To disable the escape mechanism, use [NOT] LIKE ... ESCAPE ''.

You can use keyword ILIKE instead of LIKE to make the match case-insensitive according to the active locale.

The following table describes the operator that is equivalent to each keyword that can be used in the LIKE pattern matching.

Keyword	Equivalent operator
LIKE	~~
ILIKE	~~*
NOT LIKE	!~~
NOT ILIKE	!~~*

Extreme DPS allows you to use LIKE, ILIKE, NOT LIKE, and NOT ILIKE as operators. For example, you can use any of them in <expression> <operator> ANY (<subquery>) constructs. The only difference is that in such constructs you cannot include the ESCAPE clause. However, in some obscure cases, you cannot use these keywords to replace their counterpart operators.

---

Regular expression functions

Regular expression functions are string functions used to perform operations involving regular expression (regex) matching.

Supported regular expression functions

Extreme DPS supports the use of regular expression functions for pattern matching, as described in the following table.

Function	Return type	Description
REGEXP_REPLACE(subject text, pattern text)	text	Replaces all substrings in the specified <subject> string that match the <pattern> with an empty string.
REGEXP_REPLACE(subject text, pattern text [, replacement text])	text	Replaces all substrings in the specified <subject> string that match the <pattern> with the string specified by <replacement>.
REGEXP_LIKE(subject text, pattern text)	boolean	Returns true if the specified <subject> string matches the <pattern>.
REGEXP_EXTRACT(subject text, pattern text)	text	Returns the first substring in the specified <subject> string that matches the <pattern>.
REGEXP_EXTRACT(subject text, pattern text, group integer)	text	Finds the first substring in the specified <subject> string that matches the <pattern> and returns the text of the specified <group> number.

Examples

-- Examples of REGEXP_REPLACE(subject text, pattern text)
SELECT REGEXP_REPLACE('fun stuff.', '[a-z]')  →  ' .'
SELECT REGEXP_REPLACE('call 555.123.4444 now', '(\d{3})\.(\d{3}).(\d{4})')  → 'call  now'

-- Examples of REGEXP_REPLACE(subject text, pattern text [, replacement text])
SELECT REGEXP_REPLACE('fun stuff.', '[a-z]', '*')  → '*** *****.'
SELECT REGEXP_REPLACE('call 555.123.4444 now', '(\d{3})\.(\d{3}).(\d{4})', '($1) $2-$3') → 'call (555) 123-4444 now'

-- Examples of REGEXP_LIKE(subject text, pattern text)
SELECT REGEXP_LIKE('Stevens', 'Ste(v|ph)en') → true
SELECT REGEXP_LIKE('Hello', '^[a-zA-Z]+$') → true
SELECT REGEXP_LIKE('12345', 'x') → true

-- Examples of REGEXP_EXTRACT(subject text, pattern text)
SELECT REGEXP_EXTRACT('Hello world bye', '\b[a-z]+') → 'world'
SELECT REGEXP_EXTRACT('12345', 'x') → null

-- Examples of REGEXP_EXTRACT(subject text, pattern text, group integer)
SELECT REGEXP_EXTRACT('Hello world bye', '\b[a-z]([a-z]*)', 1) → 'orld'
SELECT REGEXP_EXTRACT('rat cat\nbatdog', 'gg(.)|ba(.)(.)', 2) → 't'