Click here to Skip to main content
14,773,753 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
select EmpName,Geo from tblemployees ///result: JOHN 1,2,4,7

select * from tblGeoMaster ///result coulmns-->Geo_id, Geo_Name

how to fetch mapped mapped Geo_Names for JOHN.

What I have tried:

I need the SQL logic to fetch the split the comma separated value then find the geo_name
Posted
Updated 7-Dec-20 21:41pm
Comments
Maciej Los 8-Dec-20 5:50am
   

1 solution

Basically don't. You can do it, but it's clumsy: Converting comma separated data in a column to rows for selection[^]

A much, much better idea is to use use a separate table to store each value in your comma delimited data in it's own row, with a foreign key back to the original row data. And if the separated data items are row ids for another table - as they would appear - you can use a foreign key there as well.

You can then use JOIN to build your data really easily, and all processing becomes simpler. For example, John no longer needs the geo-name "2": how do you remove that from the string in SQL? What if he no longer needs 2 and 4? What if you need to remove geo-name 42 from all references? With a separate table to cross reference them, it all becomes trivial. When storing CSV data in a column, SQL's limited string handling becomes horribly apparent.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900