CODEWITHSUNDEEP
sqlpostgresql-9.3
adn
adn

Reputation: 430

Split Word in table field into new row

I have postgres table like this select name from product

IMAGE

left side is the result of the query now, and the right side is what it should be.

How can i make it? thanks

Upvotes: 0

Views: 138

Answers (2)

YK S
YK S

Reputation: 136

You can use regexp_split_to_table to split the words. Assuming your table name is "product" and the column is "name", here is the sql:

SELECT regexp_split_to_table(name, E'\\s+') as name FROM product;

Full testing SQL with result is attached below:

create table product(
    name varchar(200)
    );
insert into product values('microsoft office');
insert into product values('virtual studio');
insert into product values('adobe reader');
insert into product values('adobe photoshop 9');
insert into product values('paint');
insert into product values('google chrome');

SELECT regexp_split_to_table(name, E'\\s+') as name FROM product;

Result:

    name
1   microsoft
2   office
3   virtual
4   studio
5   adobe
6   reader
7   adobe
8   photoshop
9   9
10  paint
11  google
12  chrome

Upvotes: 2

zarruq
zarruq

Reputation: 2465

You can use split_part function and union to achieve you desired result as below.

select * from (
SELECT split_part(c1, ' ', 1) as Name from t1
union all
SELECT split_part(c1, ' ', 2) from t1
) t
where t.Name <> ''
order by 1;

outer query is used to filter out if there is any empty string output e.g. in case of paint.

Result:

name
----------
adobe
chrome
google
microsoft
office
paint
reader
studio
virtual

You can check demo here

Upvotes: 0

Related Questions