Home > Mobile >  Extract strings with multiple words in Postgres 11.0
Extract strings with multiple words in Postgres 11.0

Time:04-12

I have following column in Postgres table. I would like to only get values where there are multiple words in a string.

col1
nilotinib hydrochloride
ergamisol
ergamisol
methdilazine hydrochloride

The desired output is

col1
nilotinib hydrochloride
methdilazine hydrochloride

I am using following pattern to extract the strings but it's not working

SELECT regexp_match(col1, '^\w \s .*') from tb1;

CodePudding user response:

To filter rows, use a WHERE clause in your statement:

SELECT col1
FROM tb1
WHERE col1 ~ '^\w \s .*';

See the string matching documentation for alternatives to your pattern. For your case, col1 ~ '\s' should be sufficient, or col1 SIMILAR TO '%[[:space:]]%'.

  • Related