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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try
SELECT ID FROM myTable WHERE Instr("," & [myField] & ",", ",21,") > 0
SELECT ID FROM myTable WHERE Instr("," & [myField] & ",", ",21,") > 0
ASKER
Nice one Dany, excellent solution - works a treat
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
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
In access instead of % you use *