Solved

SQL Question

Posted on 2015-02-11
5
104 Views
Last Modified: 2015-02-12
Hello,

I have a SQL Server table with field (let's call it "Categories") that contains a list of IDs (e.g.: 1,3,5,11)

I want to find all rows where Categories contains the number 1 (but not 11)

Is this possible in standard SQL and, if so, how?

Thanks in advance.
0
Comment
Question by:skbohler
  • 2
  • 2
5 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40603359
You mean really the number 1 (unit) and not any number with the digit 1 (10...19, 21, 31, ..., 100..199, ...)?
Also, define "list of IDs". It's a string with numbers separated by commas?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40603627
skbohler, Vitor is good enough with SQL that I think he'll get you a solution. (Points to him, not me :)  

But PLEASE, before you DREAM of designing another table with a comma-delimited list, read up on First Normal Form!  You'll save yourself all kinds of troubles like this!
0
 

Author Comment

by:skbohler
ID: 40603635
Daniel,

Yeah, I wish the previous developer hadn't built the DB with the comma-delimited list.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40603646
Ok, understood.  I've inherited some horrible code in my time too. ;)
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40605092
So, it's really a comma-delimited. If you want to find only the number 1 and assuming that the list is ordered then you can use the following code:
SELECT *
FROM Categories
WHERE CatID='1' OR CatID LIKE '1,%'

Open in new window


Just in case of the list isn't ordered then use this code:
SELECT *
FROM Categories
WHERE CatID='1' OR CatID LIKE '1,%' OR CatID LIKE '%,1,%' OR CatID LIKE '%,1'

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

862 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now