如何从Postgres中的字符串中提取特定单词

Question

产品名称包含由空格分隔的单词。 第一个字是品牌规模第二等

如何提取字符串的那些话，情商如何实现像查询：

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

如何Postgres的创建getwordnum（）函数或应该有的子（ ）或其他函数直接在这个查询中使用以提高速度？

Answer 1

你可以尝试使用功能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}) 

Answer 2

你要找的可能是split_part它可以在PostgreSQL中作为字符串函数使用。请参阅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})