469,909 Members | 1,748 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

How do you do a query to find a 1-digit number in a comma separated list?

I am trying to figure out how to do a query in Access 2000 that will find all records that contain the number 1. The problem is that the particular field I am searching has lists of numbers in it separated by commas. For example, the field may contain the numbers 1,71,38,81. (The field is formatted as a text field) I want to pull up all the records that contain the number 1, but if I search for 1, I also get records that contain 71 or 61 or some other number that contains a 1 as one of its digits.

I WOULD want it to return the above example, because it contains a 1 by itself, but I would NOT want it to return the following: 61,83,85 because it does not contain the number 1 by itself.

Is there some way to specify that I am searching for a single character? That would mean YES to "1" but NO to "61"?
Aug 28 '07 #1
3 1654
JKing
1,206 Expert 1GB
Have you tried using the like operator and specifying the commas.

Example: WHERE field LIKE ',1,'
Aug 28 '07 #2
mlcampeau
296 Expert 100+
Have you tried using the like operator and specifying the commas.

Example: WHERE field LIKE ',1,'
I had the same thought as JKing, but you will want to take into consideration that 1 could be in the first place in the list or last place as well. JKing's example would take into consideration that 1 is in the middle of the list. To take care of the other situations try this:

WHERE field LIKE '1,*' OR field LIKE '*,1,*' OR field LIKE '*,1'
Aug 28 '07 #3
missinglinq
3,532 Expert 2GB
I think the more important question here is exactly what kind of data are you storing in this field? It sounds like you're storing multiple values in a single field which is very, very poor practise, and leads to all kinds of problems, as I believe you've already discovered. Perhaps we could offer you an alternative approach if we knew a little bit more about your situation.

Linq ;0)>
Aug 28 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by netpurpose | last post: by
1 post views Thread by Nicolae Fieraru | last post: by
1 post views Thread by hardik | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.