CODEWITHSUNDEEP
sqlmysqlwildcard
sydlawrence
sydlawrence

Reputation: 1892

Mysql query with wildcard on number strings

I am trying to query a mysql table which contains strings of numbers (i.e. '1,2,3,4,5').

How do I search to see if it has '1' but not '11' bearing in mind if it is '9,10' '9%' doesnt work??

Fixed!

(field like '10' OR field like '%,10,%' OR field like '%,10' OR field like '10,%')

Upvotes: 1

Views: 4833

Answers (3)

Tomalak
Tomalak

Reputation: 338316

Standard SQL can do it as well:

...
WHERE 
  ',' + SetValue + ',' LIKE '%,1,%' 
  AND ',' + SetValue + ',' NOT LIKE '%,11,%'

This expression cannot make use of an index, therefore performance will degrade quickly as the table size rises.

For better performance your table should be properly normalized, e.g.

SetId  SetValue
    1  1
    1  2
    1  3
    1  4
    1  5

instead of

SetId  SetValue
    1  '1,2,3,4,5'

Upvotes: 0

soulmerge
soulmerge

Reputation: 75724

You need the function FIND_IN_SET. Btw, '9%' should work, if the column contains the values you specified, are you sure you're querying

SELECT * FROM table WHERE field LIKE '9%'?

Upvotes: 1

Jimmy Stenke
Jimmy Stenke

Reputation: 11220

You could try the function find_in_set

select find_in_set('1','1,2,3,11,12')

Upvotes: 2

Related Questions