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

Wiktor Stribiżew · Accepted Answer · 2019-02-20 08:19:13Z

10

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:

edited Feb 20, 2019 at 8:19

answered Aug 26, 2017 at 20:31

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL find where string contains exact string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related