[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-18
2
Medium Priority
?
205 Views
Last Modified: 2012-05-05
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
Comment
Question by:nyk001
2 Comments
 
LVL 14

Accepted Solution

by:
Binuth earned 500 total points
ID: 22991685
try this
SELECT TABLE1_ID,TABLE1_ITEMS FROM TableName
WHERE CHARINDEX(',8,',',' + TABLE1_ITEMS + ',') > 0

Open in new window

0
 
LVL 1

Assisted Solution

by:gtsupport
gtsupport earned 500 total points
ID: 22991766
SELECT TABLE1_ID, TABLE1_ITEMS
FROM TABLE1

WHERE  TABLE1_ITEMS  LIKE  '8,%' OR  TABLE1_ITEMS  LIKE  '%,  8,%'
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question