• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

SQL use SELECT to search for records from a field containing a list of number separated by commas

We have a database that has a field in who contents are lots of strings of numbers separated by commas.

We want to do a select to extract records where this field contains one specific number.

eg

Example of this field could be "15,21,32,45,65,68"

And we want to select it using something like this:
- SELECT ID FROM myTable WHERE myField CONTAINS '21';

So it selects the record if the number exists anywhere in the field, whether it be on it's own or within a list of numbers.
0
Sean Clarke
Asked:
Sean Clarke
1 Solution
 
Dany BalianCTOCommented:
Select * from mytable where myfield like '%21%'
will give u what u want however it will also list records not containing 21 but actually containing 121 example: 17,121,34,36,45

If u don't want that then u change ur select to:

Select * from mytable where ','&myfield&',' like '%,21,%'
And it will only give u fields with 21 in it
0
 
Dany BalianCTOCommented:
Just one correction:
In access instead of % you use *
0
 
Rey Obrero (Capricorn1)Commented:
try

SELECT ID FROM myTable WHERE Instr("," & [myField] & ",", ",21,") >  0
0
 
Sean ClarkeAuthor Commented:
Nice one Dany, excellent solution - works a treat
0
 
PatHartmanCommented:
This is a poor design strategy since it violates first normal form and will prove difficult to work with.

To search the string, you would need to use the LIKE operator.  Using the LIKE operator in this case will force a full table scan.  No index can be used and no optimization is possible by the query engine.  Every single record in the table must be examined including every single character in the mushed field.  If you have more than a few thousand rows in this table, you will be verrrry unhappy with performance.

Where SomeField LIKE "*" & Forms!yourField & "*";

Keep in mind that embedded strings will cause confusion.  So if you search for 12, you'll get 12, 1244, 45124, etc.

I've attached a word document with the text of two articles (including links to the source) that show all the Access wild card options.
AccessWildCards.docx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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