SQL Question

Posted on 2015-02-11
Last Modified: 2015-02-12

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.
Question by:skbohler
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 50

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?
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!

Author Comment

ID: 40603635

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

Expert Comment

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

Accepted Solution

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:
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:
FROM Categories
WHERE CatID='1' OR CatID LIKE '1,%' OR CatID LIKE '%,1,%' OR CatID LIKE '%,1'

Open in new window


Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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