?
Solved

SQL Question

Posted on 2015-02-11
5
Medium Priority
?
109 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
[X]
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
5 Comments
 
LVL 51

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 51

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

777 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