LIKE
The database supports various pattern matching methods. The first one is LIKE or NOT LIKE.
Sample code | Result |
‘abc’ LIKE ‘abc’ | true |
‘abc’ LIKE ‘abcz’ | false |
‘abc’ LIKE ‘a%’ | true |
‘abc’ LIKE ‘_a_’ | false |
‘abc’ LIKE ‘a_’ | false |
‘abc’ LIKE ‘a’ | false |
SIMILAR TO
The second pattern matching method is SIMILAR TO, which is supported by the database. It works similar to LIKE, but supports more matching syntax. See the table below for details.
Syntax | Description | Sample code | Result |
| | Supports optional matching | 'abc' SIMILAR TO '%(b|d|c|e)%' | True |
* | The preceding element repeated 0 or more times | 'abc' SIMILAR TO 'abcd*' | True |
'abc' SIMILAR TO 'abc*' | true | - | - |
'abc' SIMILAR TO 'ab*' | false | - | - |
+ | The preceding element repeated 1 or more times | 'abc' SIMILAR TO 'abc+' | True |
'abc' SIMILAR TO 'abcc+' | false | - | - |
POSIX Regular Expressions
The database also supports POSIX regular expressions, which provide more powerful functions than LIKE and SIMILAR TO operators. POSIX regular expressions supported functions are shown in the table below.
Function | Feature | Sample code | Result |
substring(string from pattern) | Extract a substring from string according to the regular pattern | substring('foobar' from 'o.b') | oob |
regexp_replace(source, pattern, replacement [, flags ]) | Replace the matched string in the source | regexp_replace('foobarbaz', 'b..', 'X') | fooXX |
regexp_matches(string, pattern [, flags ]) | The function returns a text array, which consists of all captured substrings obtained by matching a POSIX regular expression pattern | regexp_matches('foobarbequebaz', '(bar)(beque)'); | {bar,beque} |
regexp_split_to_table(string, pattern [, flags ]) | The function splits a string using a POSIX regular expression pattern as a delimiter | SELECT regexp_split_to_table('the quick brown fox jumped', E'\\s+') ; | the quick brown fox jumped |
regexp_split_to_array(string, pattern [, flags ]) | Similar to regexp_split_to_table, it is a regular expression split function, but it returns the result as a text array | SELECT regexp_split_to_array('the quick brown fox jumped', E'\\s+'); | {the,quick,brown,fox,jumped} |