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

Select from comma delimited list previously saved in a char field

I have a table with, among other fields, a varchar field named "Countries" that contains a comma delimited lists of country codes ( ie "28,76,78,98")
How can i select for instance all records where the id 78 is in that Countries list?

Thanks
Jaime
0
GreatSolutions
Asked:
GreatSolutions
  • 3
  • 2
1 Solution
 
SimonCommented:
where countries like '%[^0-9]78[^0-9]%'
0
 
GreatSolutionsC.I.OAuthor Commented:
Hi
It doesn't work
0
 
Neil RussellTechnical Development LeadCommented:
The worst possiible way to use SQL fields is to try storing compound data in them.  One field should contain one value and nothing more.  
Storing complex data like that slows your system down by massive amounts.  A linked table with two fields in it would have been your answer.

NewTable:CountryLinks
int ReferencID
int CountryCode

The referenceID is the key of the record that originally held your "Countries" field and CountryCode is each of the individual elements from your Counries field.

So assuming that your example above where you had  country codes ( "28,76,78,98") in a row with an ID field value of 129, you would have entries of....
ReferenceID  CountryCode
129                  28
129                  76
129                  78
129                  98

Now you can easily select anything in a normal, indexed, SELECT statement joining the two tables.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
GreatSolutionsC.I.OAuthor Commented:
I understand Neil but in this case i must use the field with the concat values in it...
0
 
Neil RussellTechnical Development LeadCommented:
Without resulting to CLR then Off the top of my head your going to need to use the VERY slow...

SELECT * from tab1 where countries LIKE "78,%" OR countries LIKE "%,78" or countries LIKE "%,78,%"
0
 
GreatSolutionsC.I.OAuthor Commented:
Changed the double quotes to single, and also added "or countries='78' for the case where there is only one country. Tested and it works correctly. I don't mind about speed, this is a table with a few hundred records at the most

Many thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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