PostgreSQL find where string contains exact string

Question

I have a table with values like:

• "Head of HR"
• "Assistance of management"

If I have the string like "hr" I would like to find the row "Head of HR". A simple "LIKE %hr%" would not be precise enough, because other rows with a string containing "hr" would be found as well. I guess I need some kind of regex.

Maybe someone could give me a hint, how to write it.

Thanks Alex

Answer 1

Use

WHERE col ~ '\yHR\y'

The \yHR\y regex will match a HR as a whole word (the \y is a word boundary, it matches the start or end of a word).

Rextester demo:

CREATE TABLE tabl1
    (s character varying)
;

INSERT INTO tabl1
    (s)
VALUES
    ('Head of HR'),
    ('Assistance of management'),
    ('CHROME')
;

select * from tabl1 where s ~ '\yHR\y';

Result: