Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Question

Posted on 2015-02-11
5
Medium Priority
?
110 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 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

772 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