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
不,我正在寻找sql查询从这些行搜索逗号分隔字符串说我可以搜索扇区A,扇区f在这种情况下,行1,行2,行3值应打印为扇区A在行1,行2和扇区f在第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
和loc是扇区A,扇区f
1 个解决方案
#1
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).
而不是在主表中使用逗号分隔值的“位置”列,你真正应该拥有的是第二个表。我将打电话给您的第一个表inventory_item,并假设您正在尝试跟踪该库存所在的位置(因为您没有说明您的应用程序的作用)。
So add a table called inventory_item_location with columns:
因此,添加一个名为inventory_item_location的表,其中包含以下列:
id, inventory_item_id, location
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.
您将在inventory_item_location表中的每个位置有一行,而inventory_item_id将是inventory_item表的ID。那么您只需查询inventory_item_location表,查找您正在寻找的任何行业。你知道那个地方有什么物品。
#1
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).
而不是在主表中使用逗号分隔值的“位置”列,你真正应该拥有的是第二个表。我将打电话给您的第一个表inventory_item,并假设您正在尝试跟踪该库存所在的位置(因为您没有说明您的应用程序的作用)。
So add a table called inventory_item_location with columns:
因此,添加一个名为inventory_item_location的表,其中包含以下列:
id, inventory_item_id, location
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.
您将在inventory_item_location表中的每个位置有一行,而inventory_item_id将是inventory_item表的ID。那么您只需查询inventory_item_location表,查找您正在寻找的任何行业。你知道那个地方有什么物品。