There are a few approaches available for pattern matching and string searches in Postgres.
LIKE:
The simplest method is LIKE. LIKE is a standard SQL operator. You can compare a string to a pattern, using the % wildcard for matching any number of characters, or _ for one character.
For example, you can look for strings that start with the letters RE:
LIKE ‘RE%’;
or search for where the second character is a E:
LIKE ‘_E%’;
If you need to search for a % or _ you can use a backslash as an escape character.
To find strings that contain an underscore:
LIKE ‘%\_%’;
ILIKE can be used for case insensitive searches.
You can also use ~~ instead of LIKE, ~~* instead of ILIKE, and ! for a NOT.
SIMILAR TO:
Postgres also has a SIMILAR TO function, that looks like something between LIKE and Regular Expressions. It uses a SQL standard version of regular expression, so it is somewhere in between LIKE and full Regular Expressions.
Postgres RegEx:
This is certainly the most powerful (and most complicated) of the three search methods.
There is a REGEXP_MATCHES function that takes the string and applies the pattern. There’s also a third optional flags parameter, such as i for case insensitive match.
For example, here is a query to find all table names that start with a lower case t:
SELECT table_schema, table_name,
REGEXP_MATCHES(table_name, '^[t]')
FROM information_schema.tables;
There is also a REGEXP_REPLACE function that will replace the matched string with another string.
We can also use ~ to match, ~* for a case insensitive match, and ! for NOT.
For the above REGEXP_REPLACE example for table names, we would use:
SELECT table_schema, table_name FROM information_schema.tables WHERE table_name ~ '^[t]';
Reg Ex:
^ = Beginning of string
$ = End of string
. = any character
\d – Single digit
\d{2} – two digits
\d+ – One or more digits
* = 0 or more
+ = 1 or more
\b = word boundary
[A-Z] = Range of characters
(1|2) = 1 or 2
\ = Escape character
Links:
Postgres – Pattern Matching
SQL Rob 