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

Question

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]

Please show several rows of sample data which make clear what you are trying to do here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 4, 2017 at 5:20

halfer · Accepted Answer · 2017-07-04 08:26:58Z

3

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'

edited Jul 4, 2017 at 8:26

halfer

20.2k19 gold badges110 silver badges207 bronze badges

answered Jul 4, 2017 at 5:30

G one

2,7392 gold badges16 silver badges18 bronze badges

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

2 Comments

Brendan O'Brien Over a year ago

Oops, sorry about not accepting the answer. If you have a moment, I'm wondering what the 'end as "Result" in the query is for?

G one Over a year ago

END is to end the case statement, it is a syntax. AS "result" is just an alias column name to the resultant column.

Mohit · Accepted Answer · 2017-07-04 05:37:10Z

0

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.

answered Jul 4, 2017 at 5:37

Mohit

114 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related