Sql like statement - Returns wrong result

Question

I know the title is not good i don't know how to explain that.

I have table like

Id    AvaibleSports
1     [1]
2     [1]
3     [2],[10]
4     [4],[10]

I know this is not the best way to store information but this is all i have right now. I need to filter rows based on avaible sports value.

For example when i filter for "1" i must get records 1 and 2. When i filter for "10", i need to get 3 and 4 back.

I tried to filter with like statement like this:

select Id, AvaibleSports FROM myTable wHERE AvaibleSports like '%[10]%'

but this is also returning records containing [1].

I also tried to filter like

select Id, AvaibleSports FROM myTable wHERE AvaibleSports like '%10%'

This is returning correct result for 10 but wrong result for 1.

How can i filter this correctly?

Why do you store multiple values in one column? Instead store the right type in another table which is linked with this via foreign-key. — Tim Schmelter, Apr 20 '15 at 07:02
Like the dup says: `AvaibleSports like '%\[10\]%' escape '\'` — Patrick Hofman, Apr 20 '15 at 07:04
@wewesthemenace: Possibly, but I think `escape` is an ANSI standard. — Patrick Hofman, Apr 20 '15 at 07:05

score 1 · Answer 1 · answered Apr 20 '15 at 07:07

you can alternately use CHARINDEX . Something like this.

DECLARE @table1 TABLE
(Id INT IDENTITY(1,1),
AvaibleSports VARCHAR(10)
)
INSERT INTO @table1(AvaibleSports)
SELECT '[1]' a
UNION ALL SELECT '[1]'
UNION ALL SELECT '[2],[10]'
UNION ALL SELECT '[4],[10]'

SELECT * FROM @table1 WHERE CHARINDEX('[10]',AvaibleSports) > 0

Sql like statement - Returns wrong result

1 Answers1