How can I use pattern matching in Postgres

Question

I have a basic tables of books like so:

CREATE TABLE books (
  id integer primary key,
  name text not null
);
INSERT INTO books (id, name) VALUES (1, 'The Ghost');

I want to search for a book name which matches the search term ^The Ghost$. As you can see there is some regular expression in the term. How do I match via the regular expression?

I tried doing this but I got no results

select *
from books
WHERE name like '%^The Ghost$%'

SQL fiddle: http://sqlfiddle.com/#!17/8a5aa6/1

score 0 · Accepted Answer · answered Nov 16 '20 at 05:54

0

Well if your requirement really be verbatim to match ^The Ghost$, then you may just use an equality comparison here:

SELECT *
FROM books
WHERE name = 'The Ghost';

If you wanted to express the above using regex, you could use the ~ operator:

SELECT *
FROM books
WHERE name ~ '^The Ghost$';

answered Nov 16 '20 at 05:54

Tim Biegeleisen

451,927
24
239
318

Can `~` be used instead of `LIKE` for terms without any regex? Will it still be able to match partial book names – Paul Nov 16 '20 at 05:58
Answer: Maybe. It depends on the regex/like expression. – Tim Biegeleisen Nov 16 '20 at 05:59
Would `name LIKE '%The Ghost%'` be the same as `name ~ 'The Ghost'` – Paul Nov 16 '20 at 06:01
Yes, those two expressions in this case would match the same things. – Tim Biegeleisen Nov 16 '20 at 06:03

How can I use pattern matching in Postgres

1 Answers1