Find Where string contains keywords

Question

User inputs some keywords that he wants to get a notification, when a new post title contains these. Here is what I'm currently trying:

LOCATE( LOWER( REPLACE(  `app_notif_keywords` ,  ' ',  ',' ) ) , LOWER( REPLACE(  'Visiska tamsa | Pitch Black (2000)',  ' ',  ',' ) ) ) !=0

Here is a stripped down version:

LOCATE('the,gift,pitch,black', 'visiska,tamsa,|,pitch,black,(2000)')

However, I'm getting nothing returned. What can I use instead to make it work?

Full text index. That would be the appropriate technology for this type of problem. — Gordon Linoff, Nov 27 '15 at 15:57
I think I'm just using a wrong sql function, is there any function to check whether both arrays have any same value containing? E.g. 1,2,3 and 4,5,6 don't have same values, but 1,2,3 and 5,3,4 has. — Gintas_, Nov 27 '15 at 16:02
Which db are you using? mysql, oracle, sql server, postgres, etc? — Bohemian, Nov 27 '15 at 17:20

score 1 · Answer 1 · answered Nov 27 '15 at 22:05

1

To match any of a range of csv values, use regexp like this:

select * from mytable
where lower(concat(',', app_notif_keywords, ','))
  regexp lower(concat(',(', replace('Visiska tamsa Pitch Black', ' ', '|'), '),'))

See SQLFiddle

answered Nov 27 '15 at 22:05

Bohemian

412,405
93
575
722

Find Where string contains keywords

1 Answers1