How to extract a specific word from a string in Postgres

Question

The product name contains words separated by a space. The first word is the second place in the brand, etc.

How to extract these words from a string, eq, how to implement a query like:

select id, getwordnum( prodname,1 ) as size, getwordnum( prodname,2 ) as brand from products where ({0} is null or getwordnum( prodname,1 )={0} ) and ({1} is null or getwordnum( prodname,2 )={1} ) create table product ( id char(20) primary key, prodname char(100) ); 

How to create the getwordnum () function in Postgres or use some substring () or another function used directly in this request to improve speed?

Answer 1

You can try using the split_part function

 select id, split_part( prodname, ' ' , 1 ) as size, split_part( prodname, ' ', 2 ) as brand from products where ({0} is null or split_part( prodname, ' ' , 1 )= {0} ) and ({1} is null or split_part( prodname, ' ', 2 )= {1} ) 

Answer 2

What you are looking for is probably split_part , which is available as a String function in PostgreSQL. See http://www.postgresql.org/docs/9.1/static/functions-string.html .

Answer 3

 select id, prodname[1] as size, prodname[2] as brand from ( select id, regexp_split_to_array(prodname, ' ') as prodname from products ) s where ({0} is null or prodname[1] = {0}) and ({1} is null or prodname[2] = {1})