Substrings in a loop separated by a comma in a record

Question

Consider the entry DOMAIN={domain1,domain2,domain3} , how can I check if domain1 or domain2 or domain3 is in the list?

For example: list={domain5, domain 2, domain6}

 ------------------------------------ | CONTEXT | DOMAIN | -----------------------------------| | context1 |domain1,domain2,domain3| |----------------------------------| 

I tried SELECT * FROM table WHERE SUBSTRING_INDEX(domain, ',', 1) IN {'domain5', 'domain 2', 'domain6'} , but this only works for the first value before the comma (domain1)

Answer 1

For SQL Server, use a custom function from CodeProject

Answer 2

with mysql you can concatenate your field and use regexp as follows:

 SELECT * FROM table1 WHERE CONCAT(',', domain, ',') REGEXP ',domain1,|,domain2,|,domain1,' 

Answer 3

Why don't you just use the LIKE operator with a percentage (%) wildcard.

 Select * From tablename where DOMAIN Like '%domain1%' or DOMAIN Like '%domain2%' or DOMAIN Like '%domain3%'