postgresql - Postgres regex 子串或 regexp_matches

Question

过去几天我一直在尝试计算以下内容。

表1:同义词

Id         Synonym       code

------------------------------
1          Car Tyre      001

2          Bike Tyre     002

3          Cycle Tyre    003

4          Hammer Tube   001

现在我的输入 = 'WITH CAR TIRE FROM Hammer Tube AUDI 2000'

输出 = 两次列出代码“001”，因为输入文本同时包含“CAR TYRE”和“Hammer Tube”

当我尝试下面的查询时，它只显示一次，但我需要两次

Select * from synonym where 'WITH CAR TYRE FROM Hammer Tube AUDI 2000' ~ Synonym;

Answer 1

结合使用 position() 和 lower():

with synonym(id, synonym, code) as (
values
    (1, 'Car Tyre', '001'),
    (2, 'Bike Tyre', '002'),
    (3, 'Cycle Tyre', '003'),
    (4, 'Hammer Tube', '001')
)

select *
from synonym
where position(lower(synonym) in lower('WITH CAR TYRE FROM Hammer Tube AUDI 2000')) > 0

 id |   synonym   | code 
----+-------------+------
  1 | Car Tyre    | 001
  4 | Hammer Tube | 001
(2 rows)