Postgres regex begins with ^ and ends with $ using LIKE

Question

Newbie question. I'm looking for stings beginning with letter 'R' this works:

SELECT *
FROM table
WHERE column LIKE  'R%'

this does not

SELECT *
FROM table
WHERE column LIKE '^R%'

WHY? Also just curious, if I want it to end with R, I use the $?

'%R$'

Pattern matching is not explained well here. If you know any other resources for a layman please share. thanks https://www.postgresql.org/docs/9.3/static/functions-matching.html

Do you mean the pg docs on pattern matching aren't sufficient? What isn't "explained well" for you? — 1252748, Oct 27 '17 at 15:44
That documentation clearly shows that `LIKE` has its own syntax, which is different from regex, which is applied using `SIMILAR TO`. Nowhere does it indicate that you can mix or match arbitrarily and expect it to know which kind of pattern-matching you want at any given time. — underscore_d, Oct 27 '17 at 15:45

score 22 · Answer 1 · edited Aug 07 '19 at 06:33

The manual is very clear LIKE and regular expressions are two different things.

LIKE

Accordingly to the manual:

The LIKE expression returns true if the string matches the supplied pattern. (As expected, the NOT LIKE expression returns false if LIKE returns true, and vice versa. An equivalent expression is NOT (string LIKE pattern).)

So, LIKE returns true or false while matching a string.

% is similar to * in filesearch, it means it will match zero or any after or before.

R% R and any character after.
%R Any character befor and R

LIKE it's used instead of =, and more functionalities are explained in the manual.

-- Gets zero or any characters before one R
SELECT * FROM table WHERE column LIKE '%R'

-- Gets zero or any characters after one R
SELECT * FROM table WHERE column LIKE 'R%'

ILIKE

LIKE is case sensitive, to make it case insensitive you can use ILIKE

REGULAR EXPRESION

In postgreSQL, for a regular expression match, instead of using = you use ~ and the regular expression format is accordingly to the POSIX regular expressions standards

An example is:

-- Gets zero or any spaces numbers and text characters before one R
SELECT * FROM table WHERE column ~ '^[\s\w]*[R]{1}$'

-- Gets zero or any spaces numbers and text characters after one R
SELECT * FROM table WHERE column ~ '^[R]{1}[\s\w]*$'

In the manual there's the explanation for all the available operators for regular expressions. How to use regular expressions is other thing and it is in the POSIX regular expressions standards; that has nothing to do with PostgreSQL.

very helpful explanation, thank you. looks like I was trying to use the two interchangeably. — moonshot, Oct 28 '17 at 17:33

Postgres regex begins with ^ and ends with $ using LIKE

1 Answers1