Extract strings with multiple words in Postgres 11.0

Question

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;

Can you show us the full SQL query that you're using, please? — Bergi
– Bergi, Commented Apr 11, 2022 at 19:37

Bergi · Accepted Answer · 2022-04-11 19:48:03Z

1

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:]]%'.

answered Apr 11, 2022 at 19:48

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Extract strings with multiple words in Postgres 11.0

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