I want to do a search in my Database, that gives me the id of all fields, that a given string would contain. So if I have the string "a, b, c" and the fields a and b i want them to be shown. Is there an easy way to do it with SQL? My mind seems to be stuck right now.
I could use the LIKE operator on every possible substring of my string or just compare strings without sql, but I'm hoping for an easier, better solution.
For example: if I have my table like this:
| id | substring |
|---|---|
| 1 | 'hello' |
| 2 | 'two' |
| 3 | 'test' |
And an input-string like: "hello, this is a test"
I want to get the information, that my input sentence contains the substrings with the ids 1 and 3
In Postgresql you can use the ~ operator
WITH test AS (
SELECT 1 as id,
'hello' as str
UNION
SELECT 2 as id,
'two' as str
UNION
SELECT 3 as id,
'test' as str
)
SELECT *
FROM test
WHERE 'hello, this is a test' ~ str
Output:
id|str |
--+-----+
1|hello|
3|test |
See this query in action: https://dbfiddle.uk/Tg_ao0tm