[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

SQL Question

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
skbohler
Asked:
skbohler
  • 2
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Daniel WilsonCommented:
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
 
skbohlerAuthor Commented:
Daniel,

Yeah, I wish the previous developer hadn't built the DB with the comma-delimited list.
0
 
Daniel WilsonCommented:
Ok, understood.  I've inherited some horrible code in my time too. ;)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now