Text Operators

|| Concat​

|| concatenates strings, similar to concat().

Example​

SELECT 'a' || 'b'
concat
ab

~ Regex match​

Performs a regular-expression match on a string.

Example​

SELECT address FROM (SELECT 'abc@foo.com' as address) WHERE address ~ '@foo.com'
address
abc@foo.com

!~ Regex doesn't match​

The inverse of the ~ regex matching operator.

SELECT address FROM (SELECT 'abc@foo.com' as address) WHERE address !~ '@bah.com'
address
abc@foo.com

LIKE​

LIKE performs a case-sensitive match, based on a pattern.

The % wildcard represents 0, 1 or n characters.

The _ wildcard represents a single character.

Example​

SELECT 'abc' LIKE '%c', 'abc' LIKE 'a_c'
columncolumn1
truetrue

ILIKE​

ILIKE is the same as LIKE, but performs a case insensitive match,

Example​

SELECT 'abC' LIKE '%c', 'abC' ILIKE '%c'
columncolumn1
falsetrue