SQL select query from comma separated string

Question

I have a table with with column location with values

row1: sector A, sector B, Sector c
row 2: sector B, sector f, Sector A
row 3: sector f

No I am looking for the sql query to search from these rows with comma separated string say I can search with Sector A, sector f in that case row 1 ,row2, row 3 values should print as Sector A is in row 1, row2 and sector f is in row 3

I am trying something like this but matches the exact string only ...

SELECT id , name FROM tb1 "+
" where    Charindex(','+cast(location  as     varchar(8000))+',',',"+loc+",') > 0

and loc is sector A,sector f

You should really normalize your database and get rid of those comma separated values. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 26, 2015 at 2:37

Jason Byrne · Accepted Answer · 2015-04-26 02:59:36Z

1

Instead of having "location" column in your main table with comma separated values, what you really should have is a second table. I'm going to call your first table inventory_item and assume here you're trying to track the locations where that inventory is located (since you didn't say what your application does).

So add a table called inventory_item_location with columns:

id, inventory_item_id, location

You would have one row per location in that inventory_item_location table and the inventory_item_id would be the id of your inventory_item table. So then you just query the inventory_item_location table for whatever sector you're looking for. And you know what items are in that locaiton.

answered Apr 26, 2015 at 2:59

Jason Byrne

1,63110 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Byrne Over a year ago

Bonus: Set a unique index on inventory_item_id, location

Collectives™ on Stack Overflow

SQL select query from comma separated string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related