How can I use pattern matching in Postgres [duplicate]

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

Tim Biegeleisen · Accepted Answer · 2020-11-16 05:54:29Z

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, 2020 at 5:54

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Paul Over a year ago

Can ~ be used instead of LIKE for terms without any regex? Will it still be able to match partial book names

Tim Biegeleisen Over a year ago

Answer: Maybe. It depends on the regex/like expression.

Paul Over a year ago

Would name LIKE '%The Ghost%' be the same as name ~ 'The Ghost'

Tim Biegeleisen Over a year ago

Yes, those two expressions in this case would match the same things.

Collectives™ on Stack Overflow

How can I use pattern matching in Postgres [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related