• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

SQL Statement for checking items in field with many IDs (seperated by comma)

Hi,

I have a table setup as below:

Name: TABLE1
Fields: TABLE1_ID int, primary key, Is Identity
           TABLE1_ITEMS nvarchar (50)

Sample Data:
 TABLE1_ID               |               TABLE1_ITEMS
-----------------------------------------------------------
               1                 | 8, 14, 73, 98
-----------------------------------------------------------
               2                 | 18, 47
-----------------------------------------------------------
               3                 | 3, 8, 9, 14, 19

Is there a logical SQL Query which can select all the records from TABLE1 where the number '8' is within the range of numbers in the TABLE_ITEMS?

So in this case record 1 and 3 would match but record 2 would not.

Any help would be appreciated.

Thanks!
0
nyk001
Asked:
nyk001
2 Solutions
 
BinuthCommented:
try this
SELECT TABLE1_ID,TABLE1_ITEMS FROM TableName
WHERE CHARINDEX(',8,',',' + TABLE1_ITEMS + ',') > 0

Open in new window

0
 
gtsupportCommented:
SELECT TABLE1_ID, TABLE1_ITEMS
FROM TABLE1

WHERE  TABLE1_ITEMS  LIKE  '8,%' OR  TABLE1_ITEMS  LIKE  '%,  8,%'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now