CODEWITHSUNDEEP
postgresql
Dariel Pratama
Dariel Pratama

Reputation: 1675

get all rows preceded and followed by whitespace

for example i have the following table (let's call it, comodity)

+-----------+
|name       |
+-----------+
|kemasan    |
|fe emas ex |
+-----------+

using the following query

SELECT * FROM comodity WHERE name ILIKE '%emas%'

give me two rows kemasan and fe emas ex, however I want to select only the second row fe emas ex. how to do that?

PS: it's also SHOULD NOT preceded/followed by any visible character.

Thanks in advance

Upvotes: 0

Views: 41

Answers (1)

Kirk Roybal
Kirk Roybal

Reputation: 17857

SELECT *
FROM (VALUES
  ('blah emas blah'),
  ('vlademask'),
  ('emas1'),
  ('1emas'),
  ('emas'),
  ('1 emas'),
  ('emas 1')
) as commodity(word)
WHERE word ~* '\yemas\y'

Use a regular expression that matches words or start and stop markers.

Upvotes: 1

Related Questions