CODEWITHSUNDEEP
sqlsql-server
systemdebt
systemdebt

Reputation: 4941

How to write a SQL query that selects one of the comma separated values in the column

In my SQL Server table, there is a column in which I have comma separated values.

For example: in column AllSegmentsList and the values in the column are like 'a, b, c,d, e' .

Now, I my query I want to say 

select * 
from table 
   if one of the the entries out of all the comma separated values in that column is 'b'

but I want to make sure that the query can handle spaces on either sides of commas, if are present(not always).

Upvotes: 0

Views: 169

Answers (4)

Jayvee
Jayvee

Reputation: 10875

this should work in all cases:

 select * from tablename where replace(replace(','+allsegmentslist+',',', ',','),' ,',',') like '%,b,%'

Upvotes: 2

Daniel Freudenberger
Daniel Freudenberger

Reputation: 352

It kind of depends on your data. If no value contains a space, following should work

 select
   *
 from
   table
 where
   replace(concat(',', slist, ','), ' ', '') like '%,b,%';

Upvotes: 1

DavidG
DavidG

Reputation: 118947

If you are fine with finding only substrings, this will do:

SELECT *
FROM tablename
WHERE allsegmentslist like '%b%'

However if you need to search for the full length text and not substring, this will work:

SELECT * 
FROM tablename 
WHERE allsegmentslist like '%b,%'
OR allsegmentslist like '%,b%'
OR allsegmentslist like '%,b,%'
OR allsegmentslist = 'b'

If there are spaces after the commas, you will need to change to this:

SELECT * 
FROM tablename 
WHERE allsegmentslist like '%b,%'
OR allsegmentslist like '%, b%'
OR allsegmentslist like '%, b,%'
OR allsegmentslist = 'b'

Alternatively if the commas only sometimes have spaces after them, you will need this:

SELECT * 
FROM tablename 
WHERE allsegmentslist like '%b,%'
OR allsegmentslist like '%,b%'
OR allsegmentslist like '%,b,%'
OR allsegmentslist = 'b'
OR allsegmentslist like '%, b%'
OR allsegmentslist like '%, b,%'

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

You can just use the LIKE operator:

select *
  from tablename
 where allsegmentslist like '%, b,%'
    or allsegmentslist like 'b,%'
    or allsegmentslist like '%, b'
    or allsegmentslist = 'b'

Upvotes: 0

Related Questions