数据库字段中的逗号分隔值 [英] Comma separated values in a database field

问题描述

我有一个产品表。该表中的每一行对应一个产品，并由唯一的ID标识。现在，每个产品可以具有与该产品相关联的多个代码。例如：

I have a products table. Each row in that table corresponds to a single product and it's identified by a unique Id. Now each product can have multiple "codes" associated with that product. For example:

Id     |    Code
----------------------
0001   |   IN,ON,ME,OH
0002   |   ON,VI,AC,ZO
0003   |   QA,PS,OO,ME

我想做的是创建一个存储过程， ON，ME，并返回包含ON或ME代码的每个产品。由于代码是逗号分隔的，我不知道如何分裂和搜索它们。这是否只能使用TSQL？

What I'm trying to do is create a stored procedure so that I can pass in a codes like "ON,ME" and have it return every product that contains the "ON" or "ME" code. Since the codes are comma separated, I don't know how I can split those and search them. Is this possible using only TSQL?

编辑：这是一个关键任务表。我没有权限更改它。

It's a mission critical table. I don't have the authority to change it.

推荐答案

您应该将代码存储在单独的表中，因为一个多对多的关系。如果您将它们分开，那么您将很容易检查。

You should be storing the codes in a separate table, since you have a many to many relationship. If you separate them, then you will easily be able to check.

这将是可能在您现在的系统类型，但需要文本搜索

It would be possible to do in the type of system you have now, but would require text searching of the columns, with multiple searches per row to work, which will have huge performance problems as your data grows.

如果您尝试下载您当前的路径：
您将必须拆分输入字符串，因为不保证每个记录上的代码与输入参数处于相同的顺序（或连续）。那么你必须做一个

If you try to go down you current path : You will have to break apart your input string, because nothing guarantees the codes on each record are in the same order (or contiguous) as the input parameter. Then you would have to do a

Code LIKE '%IN%'
AND Code Like '%QA%'

查询您要检查的每个代码的附加语句。效率很低。

query with an additional statement for every code you are checking for. Very inefficient.

下面的UDF想法也是个好主意。但是，根据您的数据大小和查询和更新的频率，您也可能会遇到问题。

The UDF idea below is also a good idea. However, depending on the size of your data and the frequency of queries and updates, you may have issues there as well.

可以创建一个附加表是按照预定基准（或基于触发器）进行同步的，您可以对其进行查询？

would it be possible to create an additional table that is normalized that is synchronized on a scheduled basis (or based on a trigger) for you to query against?

这篇关于数据库字段中的逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！