PostgreSQL，取消数组到值的嵌套
PostgreSQL，取消数组到值的嵌套
提问于 2019-03-01 21:05:35
假设我有文本输入：
星期六购买，XXX送货，地址YYY，付款方式ZZZ
请给我们报价格和库存情况。
К52-1Б - 68мкф/50в
К52-1 - 100мкф/63в如何修改此查询，使其不需要手动分行？
with long_string (ls) as (values
('Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ'),
('Please quote us on prices and stock availability'),
('К52-1Б - 68мкф/50в'),
('К52-1 - 100мкф/63в')
)
select products.*, matches, similarity
from products
cross join long_string
cross join lateral
(select
(name % ls)::int as matches,
similarity(name, ls) as similarity
) m
where matches > 0 and similarity > 0.7
order by matches desc, similarity desc;我试过了
with long_string (ls) as (
values
unnest(string_to_array('Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ
Please quote us on prices and stock availability
К52-1Б - 68мкф/50в
К52-1 - 100мкф/63в', '\n'))
)
select products.*, matches, similarity
....但是有一个错误ERROR: syntax error at or near "unnest"
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-03-01 21:18:17
如果这仅仅是为了解析字符串，那么：
with long_string (ls) as (
select unnest(string_to_array(str, E'\n'))
from (values ('Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ
Please quote us on prices and stock availability
К52-1Б - 68мкф/50в
К52-1 - 100мкф/63в')
) v(str)
)
select ls
from long_string;Here是一个db<>fiddle。
Stack Overflow用户
发布于 2019-03-01 21:21:05
或者你可以这样做：
with long_string (ls) as (
select
trim(regexp_split_to_table('Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ
Please quote us on prices and stock availability
К52-1Б - 68мкф/50в
К52-1 - 100мкф/63в', '\n'))
)
select *
from long_string页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54945301
复制相关文章
相似问题
腾讯云开发者
