PostgreSQL: Combine regexp_split_to_table results into one column?

Question

I am working with a database and trying to get all the individual words that appear in 3 columns. I am SELECTing from columns title, description, and manufacturer, and using regexp_split_to_table to split into words on whitespace/punctuation/other relevant delimiters.

Right now I'm displaying 3 different columns, each with the results from title, description, and manufacturer respectively, but I want one column word where the results of all columns appear.

Here's what I have so far:

 SELECT regexp_split_to_table(trim(lower(title)), '\s+|[.()?<>!""@#$%&*;-_\/]+|[0-9]+|([0-9]+x{1}[0-9]+|[0-9]+|([0-9]+x{1}[0-9]+)')
        AS word1,
        regexp_split_to_table(trim(lower(description)), '\s+|[.()?<>!""@#$%&*;-_\/]+|[0-9]+|([0-9]+x{1}[0-9]+|[0-9]+|([0-9]+x{1}[0-9]+)')
        AS word2,
        regexp_split_to_table(trim(lower(manufacturer)), '\s+|[.()?<>!""@#$%&*;-_\/]+|[0-9]+|([0-9]+x{1}[0-9]+)\s+|[.()?<>!""@#$%&*\/]+|[0-9]+|([0-9]+x{1}[0-9]+)')
        AS word3
FROM amazon
LIMIT 500;

Wow, that's a lot of regex XD I recommend creating a temp field to combine the results into one. — user7568042
– user7568042, Commented Feb 25, 2017 at 20:40

David דודו Markovitz · Accepted Answer · 2017-02-25 21:33:28Z

1

select  regexp_split_to_table (title||' '||description||' '||manufacturer,'[^a-zA-Z]')

from    amazon

edited Feb 25, 2017 at 21:33

answered Feb 25, 2017 at 20:52

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Combine regexp_split_to_table results into one column?

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