how to extract specific word from string in Postgres

Question

Product name contains words deliminated by space. First word is size second in brand etc.

How to extract those words from string, e.q how to implement 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 getwordnum() function in Postgres or should some substring() or other function used directly in this query to improve speed ?

What "just somebody" is trying to say: your database model is wrong. Read up on normalization — user330315
– user330315, Commented Jul 15, 2013 at 11:23

xisco rossello · Accepted Answer · 2013-07-15 11:16:07Z

15

You could try to use function split_part

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} )

answered Jul 15, 2013 at 11:16

xisco rossello

2891 silver badge8 bronze badges

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

Comments

Kevin Sjöberg · Accepted Answer · 2013-07-15 11:05:07Z

0

What you're 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.

answered Jul 15, 2013 at 11:05

Kevin Sjöberg

2,25414 silver badges21 bronze badges

Comments

Clodoaldo Neto · Accepted Answer · 2013-07-15 11:37:52Z

0

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})

answered Jul 15, 2013 at 11:37

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

how to extract specific word from string in Postgres

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related