SQL Server : LIKE - Finding 1 and not 11 in a string

Question

Say I have a SQL Server table with these values:

ID     test
-----------------
1      '1,11,X1'
2      'Q22,11,111,51'
3      '1'
4      '5,Q22,1'

If I want to find out which rows contain the comma-separated value '1', I can just do the following and it will work but I'd like to find a better or less wordy way of doing so if it exists. Unfortunately I cannot use RegExp because using \b1\b would be awesome here.

Select test 
FROM ...
WHERE 
    test LIKE '1,%' 
    OR test = '1' 
    OR test LIKE '%,1' 
    OR test LIKE %,1,%

Something like...

WHERE 
    test LIKE '%[,{NULL}]1[,{NULL}]%'

I know this line isn't correct but you get what I'm after... hopefully ;)

EDITED based on comments below

can you clarify what your criteria is for selecting rows? You give example rows but not which ones should be returned. — Kevin, Mar 23 '15 at 18:17
also, your query wouldn't work because it would return a row that just had '11' in it — Kevin, Mar 23 '15 at 18:20
@Kevin He clearly states that `using \b1\b would be awesome`. That's a `1` preceded by a word boundary and succeded by a word boundary. — Giorgos Betsos, Mar 23 '15 at 18:20
@GiorgosBetsos , but that doesn't match his query which he says works. Hence my request for clarification — Kevin, Mar 23 '15 at 18:22
I should be getting back rows 1 3 and 4 because they either equal 1 or ,1 or ,1, — Ammer, Mar 23 '15 at 18:23
@Kevin Yes, that's true. I think the query is buggy; first predicate should be `'1,%'` instead of `'1%'`. — Giorgos Betsos, Mar 23 '15 at 18:24
@GiorgosBetsos your correct. I wrote it down wrong. It should be 1,% — Ammer, Mar 23 '15 at 18:25

score 5 · Accepted Answer · answered Mar 23 '15 at 18:23

You shouldn't use comma-delimited values to store lists. You should use a junction table. But, if you have to, the following logic might help:

Select test
FROM ...
WHERE ',' + test + ',' like '%,' + '1' + ',%' ;

This assumes that what you are looking for is "1" as the entire item in the list.

Note: You can/should write the like pattern as '%,1,%'. I just put it in three pieces to separate out the pattern you are looking for.

score 1 · Answer 2 · edited Mar 23 '15 at 19:44

1

select * 
from table 
where ',' + test + ',' like '%,1,%'

edited Mar 23 '15 at 19:44

marc_s

732,580
175
1,330
1,459

answered Mar 23 '15 at 18:10

Kevin

7,162
11
46
70

1

His wording isn't clear, but I read it as more than one 1 in a row. – Kevin Mar 23 '15 at 18:16

score 1 · Answer 3 · edited May 23 '17 at 12:28

There are plenty of SplitString functions available if you google around (many here on StackOverflow) that take a comma delimited string like you have, and split it out into multiple rows. You can CROSS APPLY that table-value function to your query, and then just select for those rows that have '1'.

For example, using this splitstring function here (just one of many): T-SQL split string

You can write this code to get exactly what you want (note, the declare and insert are just to set up test data so you can see it in action):

DECLARE @test TABLE (ID int, test varchar(400));
INSERT INTO @test (ID, test)
VALUES(1, '1,11,X1'),
      (2, 'Q22,11,111,51'),
      (3, '1'),
      (4, '5,Q22,1')

SELECT * 
  FROM @test
  CROSS APPLY splitstring(test)
 WHERE [Name] = '1'

This query returns this:

1  1,11,X1  1
3  1        1
4  5,Q22,1  1

Depending on the splitstring function, you can also use this technique to check if only certain columns are set to '1' as well. (most return an id / value with the id being the column #) — Kevin Cook, Mar 23 '15 at 18:51

score 0 · Answer 4 · edited Mar 23 '15 at 19:45

0

You need something like:

SELECT test 
FROM _tableName_ 
WHERE (test LIKE '1,%' 
       OR test LIKE '%,1' 
       OR test LIKE '%,1,%' 
       OR test LIKE '1')

This will return rows that match in order

1 starts a list
1 ends a list
1 is in the middle of a list
1 is its own list

edited Mar 23 '15 at 19:45

marc_s

732,580
175
1,330
1,459

answered Mar 23 '15 at 18:18

JasonKretzer

192
1
11

score 0 · Answer 5 · answered Mar 23 '15 at 18:20

0

You have to "normalize" your database. If you have multiple attributs for one row, it's a problem!
Add a "One to Many" relation with yours attributs.

You can do like that:

ID, test
1, 1
1, 11
1, X1
2, Q22
2, 11
[...]

SELECT test FROM ...
WHERE ID = (SELECT ID FROM ... WHERE test = 1)

You primary key is (ID, test) now.

answered Mar 23 '15 at 18:20

Kevin Robatel

8,025
3
44
57

Not my database but i agree with you! – Ammer Mar 23 '15 at 18:31
Ok... eventually you can create a function that split by comma in a subrequest and doing a `IN` on it. – Kevin Robatel Mar 23 '15 at 18:36

SQL Server : LIKE - Finding 1 and not 11 in a string

5 Answers5