Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Select from comma delimited list previously saved in a char field

Posted on 2014-12-14
6
117 Views
Last Modified: 2014-12-14
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
Comment
Question by:GreatSolutions
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40498824
where countries like '%[^0-9]78[^0-9]%'
0
 
LVL 2

Author Comment

by:GreatSolutions
ID: 40498846
Hi
It doesn't work
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40498847
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:GreatSolutions
ID: 40498848
I understand Neil but in this case i must use the field with the concat values in it...
0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 500 total points
ID: 40498864
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
 
LVL 2

Author Closing Comment

by:GreatSolutions
ID: 40498871
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question