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

Solution

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.

Comments

[moonshot]

very helpful explanation, thank you. looks like I was trying to use the two interchangeably.