Searching for \ and | characters in a string in PostgreSQL

Question

I am trying to search for a string that contains either a \ or / or a | character stored in column of a table.

so for an example:

I have a table called medication .

lets say the column of this medication table is brand

the brand table has some string values like:

abcef\ang\el
mghi/profe|ssional
bb/er|sup/er

i want to search for any record in the medication table that has either a \, /, or a | Character. As you can see there can be numerous occurences of these characters

If there is a match then I need to return the entire record.

Can anyone help and suggest if he following is correct or show a better way. Please note I plan to use regex as this is big table, and I am going this table with other tables as well.

this is what i did:

select m.brand 
from medication m 
where m.brand ~ '.*[\|\\\/].*'

I was trying to use regex_matches also but did not have any luck using it in where clause

also I tried this

select regex_matches(m.brand,'.*[\\\|\/].*',) from medication m

but i get kernel errors

any help would be appreciated

thanks jay

where m.brand ~ '.*[\|\\\/].*' looks like it would work, what went wrong when you tried? — Politank-Z
– Politank-Z, Commented May 9, 2015 at 3:52

Pavel Stehule · Accepted Answer · 2015-05-09 05:09:05Z

Following code works:

postgres=# SELECT * FROM foo;
┌────────────────────┐
│         a          │
╞════════════════════╡
│ abcef\ang\el       │
│ mghi/profe|ssional │
│ bb/er|sup/er       │
└────────────────────┘
(3 rows)

SELECT * FROM foo WHERE a LIKE ANY (ARRAY['%|%','%\\%','%/%']);
SELECT * FROM foo WHERE a like '%|' OR a LIKE '%\\%' OR a LIKE '%/%';
or 
postgres=# SELECT * FROM foo WHERE a ~ '[\\|/]';
┌────────────────────┐
│         a          │
╞════════════════════╡
│ abcef\ang\el       │
│ mghi/profe|ssional │
│ bb/er|sup/er       │
└────────────────────┘
(3 rows)

Attention - it can depends on PostgreSQL version. For some older PostgreSQL a "\" is a special symbol - and needs special escaping. But on 9.x line it should to work.

Collectives™ on Stack Overflow

Searching for \ and | characters in a string in PostgreSQL

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