find elements of a varchar in another varchar

Question

hi, i have a varchar field with the content like these:

a,b,c,d
e,d,a,c
b,q,d,e

i need to do a query that select only the rows with the field that has elements equals with an input string.

ex input: c,a

rows selected:

a,b,c,d
e,d,a,c

is possible without use the OR (field like '%a%' OR field like '%c%') ?

thanks

Answer 1

Yes, you can use...

WHERE
    myField LIKE '%a%' AND myField LIKE '%c%'

However, this does sound like you've got a nasty field in your SQL.

Normally, you would use a link-table to specify this kind of information:

Table1
    id
    otherdata...

Table2
    id
    letter

LinkTable
     Table1Id
     Table2Id

So in LinkTable you would have many entries that link your records. So instead of storing 'a,b,c,d' in a field, you have four link records:

Table1Id    Table2Id
1           1 (a)
1           2 (b)
1           3 (c)
1           4 (d)

Answer 2

Yes but you need a trick: Create a second column which contains the same value but sorted:

a,b,c,d
a,c,d,e
b,d,e,q

Now, you can query for %a%c%

This won't work if the query string can be a substring of any unwanted value. If this is the case, you need quotes:

"a","b","c","d"
"a","c","d","e"
"b","d","e","q"

and query for %"a"%"c"%