Postgres regex substring or regexp_matches

Question

I have been trying to figure the following for a past days.

table1: synonym

Id         Synonym       code

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

2          Bike Tyre     002

3          Cycle Tyre    003

4          Hammer Tube   001

Now My input = 'WITH CAR TYRE FROM Hammer Tube AUDI 2000'

Output = List code '001' for two times because input text contains both 'CAR TYRE' and 'Hammer Tube'

When i try this below query it shows only one time but i need twice

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

CAR TYRE is in upper case. Are you sure it would give you two result. — Fahad Anjum
– Fahad Anjum, Commented Jul 31, 2017 at 7:36
Case is not an issue, if its is lower case also no problem but i need twice in result. please help me. — Gobinath
– Gobinath, Commented Jul 31, 2017 at 7:41
postgresql.org/docs/9.0/static/functions-matching.html , Read 9.7.3. then you will get more information there. — Fahad Anjum
– Fahad Anjum, Commented Jul 31, 2017 at 7:45

klin · Accepted Answer · 2017-07-31 07:44:32Z

1

Use position() in combination with 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)

answered Jul 31, 2017 at 7:44

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Gobinath Over a year ago

Thank you its working perfectly, in this scenario now i need unique rows with count..

Collectives™ on Stack Overflow

Postgres regex substring or regexp_matches

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related