If we want to extract those rows which contain the first_name with the letter 'y' in any position from employees table, the following statement can be used. If we want to extract those rows which contain the first_name second most starting with the letter 'h' from employees table, the following statement can be used. If we want to extract those rows which contain the first_name ending with the letter 'h' from employees table, the following statement can be used. If the pattern does not match, the function returns no rows. The function can return no rows, one row, or multiple rows (see the g flag below). It has the syntax regexpmatches ( string, pattern, flags ). If we want to extract those rows which contain the first_name starting with the letter 'M' from employees table, the following statement can be used. The regexpmatches function returns a text array of all of the captured substrings resulting from matching a POSIX regular expression pattern.

Search conditions can contain either literal characters or numbers: An underscore (_) in pattern stands for (matches) any single character a percent sign (%) matches any sequence of zero or more characters.

Therefore to match a sequence anywhere within a string, the pattern must start and end with a percent sign. LIKE pattern matching always covers the entire string.Pictorial Presentation of PostgreSQL Like Operator and the NOT LIKE expression returns false if LIKE returns true. The LIKE expression returns true if the string matches the supplied pattern. There are three types of pattern matching in PostgreSQL : LIKE operator, SIMILAR TO operator, and POSIX-style regular expressions.

Postgresql regex