CODEWITHSUNDEEP
mysqlsqlcsvsubstring
Brendan O'Brien
Brendan O'Brien

Reputation: 105

SQL conditional that searches for substring and returns 'true' or 'false'

I need to do a mapping that is taxing my handle on SQL.

I need to know if a substring exists in a CSV contained in a table cell. If so, I need it to return the string 'true' else 'false'

This query works in that it returns the CSV I need to check:

select table.value from table where table.user_id = 1 and table.keyname = 'email_list'

I was attempting to include that SQL inside a conditional like this: 

IF ( (select table.value from table where table.user_id = 1 and 
table.keyname = 'email_list') LIKE '%substring%') THEN 'true' ELSE 'false'

But no luck so far. This is actually one component of a much bigger query mapping different elements from one table to another so I really need it to just return a string of 'true' or 'false' for it to work appropriately.

Table structure looks something like this:

User   KEYNAME        value (in the form of CSV)
1       email_list   [email protected], [email protected]
2       email_list   
3       email_list   [email protected]
4       email_list   [email protected], [email protected], [email protected]

Upvotes: 0

Views: 3663

Answers (2)

G one
G one

Reputation: 2729

Using a CASE statement inside the select clause should solve your problem.

Something like the below

select case when table.value LIKE '%substring%' 
        then 'TRUE' else 'FALSE' end as "Result" 
from table where table.user_id = 1 and 
table.keyname = 'email_list'

Upvotes: 2

Mohit
Mohit

Reputation: 11

SELECT table.value FROM table WHERE EXISTS (select table.value FROM table WHERE table.user_id = 1 AND table.keyname = 'email_list' AND table.value LIKE '%substring%');

I hope this will solve the issue.

Upvotes: 0

Related Questions