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进行标识，现在每个产品都可以有多个与该产品相关的“代码”。例如:
Id | Code ---------------------- 0001 | IN,ON,ME,OH 0002 | ON,VI,AC,ZO 0003 | QA,PS,OO,ME
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?
我要做的是创建一个存储过程，这样我就可以传入“ON,ME”这样的代码，并让它返回包含“ON”或“ME”代码的每个产品。由于代码是用逗号分隔的，我不知道如何分割它们并搜索它们。仅使用TSQL是否可行?
Edit: It's a mission critical table. I don't have the authority to change it.
编辑:这是一个重要的任务。我没有权力去改变它。
11 个解决方案
#1
11
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
如果您尝试沿着当前路径:您将不得不拆分输入字符串，因为不能保证每个记录上的代码与输入参数的顺序相同(或连续)。然后你要做a
Code LIKE '%IN%'
AND Code Like '%QA%'
query with an additional statement for every code you are checking for. Very inefficient.
查询为您正在检查的每个代码添加一个额外的语句。效率很低。
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.
下面的UDF想法也是一个好主意。但是，根据数据的大小和查询和更新的频率，您可能也会遇到问题。
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?
是否有可能创建一个额外的表，该表是规范化的，并在计划的基础上(或基于触发器)进行同步，供您查询?
#2
8
First, let's make the original table to become like this:
首先，让我们把原始表格变成这样:
Id | Value
-----+------
0001 | IN
0001 | ME
0001 | OH
0001 | ON
0002 | AC
0002 | ON
0002 | VI
0002 | ZO
0003 | ME
0003 | OO
0003 | PS
0003 | QA
It is accomplished by parsing the comma separated values into rows. Then use the powerful CROSS APPLY keyword to join with the original table to retrieve it's Id. Next step is simply to query this CTE.
它通过将逗号分隔的值解析成行来完成。然后使用强大的CROSS APPLY关键字与原始表连接以检索其Id。下一步就是查询这个CTE。
create function FnSplitToTable
(
@param nvarchar(4000)
)
returns table as
return
with
Num(Pos) as -- list of positions, numbered from 1 to 4000, largest nvarchar
(
select cast(1 as int)
union all
select cast(Pos + 1 as int) from Num where Pos < 4000
)
select substring(@Param, Pos,
charindex(',', @Param + ',', Pos) - Pos) as Value
from Num where Pos <= convert(int, len(@Param))
and substring(',' + @Param, Pos, 1) = ','
go
create proc ProcGetProductId
(
@Codes nvarchar(4000)
)
as
with
Src
(
Id,
Code
)
as
(
select '0001', 'IN,ON,ME,OH'
union all
select '0002', 'ON,VI,AC,ZO'
union all
select '0003', 'QA,PS,OO,ME'
),
Parse as
(
select
s.Id,
f.Value
from
Src as s
cross apply
FnSplitToTable(s.Code) as f
)
select distinct
p.Id
from
Parse as p
join
FnSplitToTable(@Codes) as f
on
p.Value = f.Value
option (maxrecursion 4000)
go
exec ProcGetProductId 'IN,ME' -- returns 0001 & 0003
#3
7
Everybody else seems very eager to tell you that you should not do this, although I don't see any explicit explanation for why not.
其他人似乎都很想告诉你，你不应该这样做，尽管我没有看到任何明确的解释。
Apart from breaking the normalization rules, the reason is that you'll do a table-scan through all rows, since you can't have an index on the individual "values" in that column.
除了打破规范化规则之外，原因是您将对所有行执行表扫描，因为您不能在该列中对单个“值”进行索引。
Simply put, there's no way for the database engine to keep some kind of quick-list of which rows contains the code 'AC', unless you either break it up into a separate table, or put it in a column by itself.
简单地说，数据库引擎不可能保持某种包含代码“AC”的快速列表，除非您将其分解为单独的表，或者将其单独放在列中。
Now, if you have other criteria in your SELECT statements that will limit the number of rows down to some manageable number, then perhaps this will be ok, but otherwise I would, if you can, try to avoid this solution and do what others have already told you, split it up into a separate table.
现在,如果您有其他条件选择语句将限制一些可控的号码记下来的行数,那么也许这将是好的,但否则我会,如果可以,尽量避免这种解决方案和做别人已经告诉你,把它分成一个单独的表中。
Now, if you're stuck with this design, you can do a search using the following type of query:
现在，如果你坚持使用这种设计，你可以使用以下类型的查询进行搜索:
...
WHERE ',' + Code + ',' LIKE '%,AC,%'
This will:
这将:
- Match 'ON,VI,AC,ZO'
- 匹配“VI,AC,佐薇”
- Not match 'ON,VI,TAC,ZO'
- 不匹配,VI,TAC,佐薇”
I don't know if the last one is a viable option in your case, if you only have 2-letter codes, then you can use just this:
我不知道最后一个在你的情况下是否是可行的选择，如果你只有两个字母的编码，那么你可以用这个:
...
WHERE Code LIKE '%AC%'
But again, this will perform horribly unless you limit the number of rows using other criteria.
但是，如果不限制使用其他标准的行数，这将会非常糟糕。
#4
5
Although all the previous posters are correct about the normalization of your db schema, you can do what you want using a "Table-Valued UDF" that takes a delimited string and returns a Table, with one row per value in the string... You can use this table as you would any other table in your stored proc , joining to it, etc... this will solve your immediate issue...
虽然前面所有的海报都是关于db模式的规范化的，但是您可以使用“表值UDF”来实现您想要的操作，该“表值UDF”接受带分隔符的字符串并返回一个表，字符串中每个值有一行……您可以像使用存储proc中的任何其他表一样使用这个表，连接到它，等等……这将解决你眼前的问题……
Here's a link to such a UDF: FN_Split UDF
这里有这样一个UDF的链接:FN_Split UDF
Although the article talks about using it to pass a delimited list of data values in to a stored proc, you can use the same UDF to operate on a delimited string stored in a column of an existing table....
尽管这篇文章谈到了使用它通过分隔的列表数据值存储过程,您可以使用相同的UDF操作一个分隔的字符串存储在现有表的一个列....
#5
4
The way you are storing data breaks normalization rules. Only a single atomic value should be stored in each field. You should store each item in a single row.
存储数据的方式违反了规范化规则。每个字段中应该只存储一个原子值。您应该将每个项目存储在一行中。
#6
4
More than 1 year old question, but still thought it will be useful. You can use the FIND_IN_SET function of MySql. I am not sure whether other DBMSs support it or not.
一个多岁的问题，但还是觉得很有用。可以使用MySql的FIND_IN_SET函数。我不确定其他dbms是否支持它。
You can use this function as follows:
您可以使用以下功能:
SELECT * FROM `table_name` WHERE FIND_IN_SET('AC', `Code`) > 0
#7
0
This might not be possible if you're stuck with that database design, but it would be a lot easier to put the codes into separate records in another table:
如果您坚持使用该数据库设计，这可能是不可能的，但是将代码放在另一个表的单独记录中要容易得多:
ProductCode
-----------
ProductID (FK to Product.ID)
Code (varchar)
The table might look like this:
这个表格可能是这样的:
ProductID Code
-----------------
0001 IN
0001 ON
0001 ME
...
The query would look something like this (you'd have to pass in the Codes somehow - either as separate variables, or maybe a comma-separated string that you split in the proc):
查询看起来是这样的(您必须以某种方式传入代码——或者作为单独的变量，或者作为您在proc中分割的逗号分隔的字符串):
select ProductID
from ProductCode
where Code in ('ON', 'ME')
#8
0
I agree with other posters here that you should look carefully into schema normalization, but I also know that shortcuts are part of life.
我同意这里的其他海报，您应该仔细研究模式规范化，但是我也知道快捷方式是生活的一部分。
Here's a sample function written in Sybase dialect that does what you do:
这里有一个用Sybase方言写的函数，它可以做你想做的事情:
ALTER FUNCTION "DBA"."f_IsInStringList"( IN @thisItem char(2), IN @thisList varchar(4000) )
RETURNS INTEGER
DETERMINISTIC
BEGIN
DECLARE is_member bit;
DECLARE LOCAL TEMPORARY TABLE tmp (thisItem char(2)) ;
DECLARE @tempstring varchar(10);
DECLARE @count integer;
IF LENGTH(TRIM(@thisList)) > 0 THEN
WHILE LENGTH(TRIM(@thisList)) > 0 LOOP
-- loop over comma-separated list and stuff members into temp table
IF LOCATE ( @thisList, ',' , 1) > 0 THEN
SET @count = LOCATE ( @thisList, ',' , 1);
SET @tempstring = SUBSTRING ( @thisList, 1,@count-1 );
INSERT INTO tmp ( thisItem ) VALUES ( @tempstring );
SET @thisList = STUFF ( @thisList, 1, @count, '' )
ELSE
INSERT INTO tmp ( thisItem ) VALUES ( @thisList );
SET @thisList = NULL;
END IF;
END LOOP ;
END IF;
IF EXISTS (SELECT * FROM tmp WHERE thisItem = @thisItem ) THEN
SET is_member = 1;
ELSE
SET is_member = 0 ;
END IF ;
RETURN is_member;
END
You can then build a simple query to check whether a value occurs in your comma-separated string:
然后，您可以构建一个简单的查询来检查值是否出现在逗号分隔的字符串中:
select * from some_table t
WHERE f_IsInStringList('OR', t.your_comma_separated_column) = 1 OR
f_IsInStringList('ME', t.your_comma_separated_column) = 1
#9
0
Although in your case simple LIKE will work, here is the solution how to parse comma separated strings Table Normalization (Parse comma separated fields into individual records).
虽然在您的例子中，简单的LIKE可以工作，但是这里提供了如何解析逗号分隔的字符串表规范化(将逗号分隔的字段解析为单独的记录)的解决方案。
#10
0
if you want to do it with php and mysql it can be any number of keyword no restriction
如果你想用php和mysql来做，可以是任意数量的没有限制的关键字
$var = explode(',',"ahmad,sayeed,asmal,babu");
$query = "SELECT * FROM post WHERE post_tags LIKE '%a%' ";
$query1=NULL;
foreach($var as $value)
{
$query1.= " OR post_tags LIKE '%$value%' ";
}
echo "$query $query1";
OUTPUT:
输出:
SELECT * FROM post WHERE post_tags LIKE '%a%' OR post_tags LIKE '%ahmad%' OR post_tags LIKE '%sayeed%' OR post_tags LIKE '%asmal%' OR post_tags LIKE '%babu%'
从post_tags(比如%a%)或post_tags(如%sayeed%)或post_tags(如%sayeed%)或post_tags(如“%asmal%”)或post_tags(比如“%asmal%”)或post_tags(如“%asmal%”)中选择*。
#11
0
1st Step : Code to create function
第一步:代码创建函数
<font face="Courier New" size="2">
<font color = "blue">CREATE</font> <font color = "blue">FUNCTION</font> <font color = "maroon">[dbo]</font><font color = "silver">.</font><font color = "#FF0080"><b>[Udflistofids]</b></font> <font color = "maroon">(</font>
<br/><font color = "green"><i>-- Add the parameters for the function here</i></font>
<br/><font color = "#8000FF">@ListOfIDs</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/><font color = "green"><i>--, @IDsSeperationChar as varchar(5) = ','</i></font>
<br/><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID1</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID2</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID3</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID4</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID5</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "maroon">returns</font> <font color = "#8000FF">@TabListOfIDs</font> <font color = "blue">TABLE</font> <font color = "maroon">(</font>
<br/> <font color = "green"><i>-- Add the column definitions for the TABLE variable here</i></font>
<br/> <font color = "maroon">id</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid1</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid2</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid3</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid4</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid5</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "blue">AS</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "green"><i>-- Fill the table variable with the rows for your result set</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@Pos</font> <font color = "blue">AS</font> <font color = "black"><i>INT</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ID</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">5</font><font color = "maroon">)</font> <font color = "silver">=</font> <font color = "red">','</font>
<br/>
<br/> <font color = "green"><i>--SET @ListOfIDs = REPLACE( @ListOfIDs, @IDsSeperationChar, ',')</i></font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ListOfIDs</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@Pos</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "silver">+</font> <font color = "red">'%'</font><font color = "silver">,</font> <font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font>
<br/>
<br/> <font color = "green"><i>--SET @Pos = CHARINDEX(@IDsSeperationChar, @ListOfIDs, 1)</i></font>
<br/> <font color = "blue">IF</font> <font color = "fuchsia"><i>Replace</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "#8000FF">@IDsSeperationChar</font><font color = "silver">,</font> <font color = "red">''</font><font color = "maroon">)</font> <font color = "silver"><></font> <font color = "red">''</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">WHILE</font> <font color = "#8000FF">@Pos</font> <font color = "silver">></font> <font color = "black">0</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ID</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>LEFT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "#8000FF">@Pos</font> <font color = "silver">-</font> <font color = "black">1</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/>
<br/> <font color = "blue">IF</font> <font color = "#8000FF">@ID</font> <font color = "silver"><></font> <font color = "red">''</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">INSERT</font> <font color = "blue">INTO</font> <font color = "#8000FF">@TabListOfIDs</font>
<br/> <font color = "maroon">(</font><font color = "maroon">id</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid1</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid2</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid3</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid4</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid5</font><font color = "maroon">)</font>
<br/> <font color = "blue">VALUES</font> <font color = "maroon">(</font><font color = "#8000FF">@ID</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID1</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID2</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID3</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID4</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID5</font><font color = "maroon">)</font> <font color = "green"><i>--Use Appropriate conversion</i></font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ListOfIDs</font> <font color = "silver">=</font> <font color = "fuchsia"><i>RIGHT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font> <font color = "silver">-</font>
<br/> <font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ID</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font><font color = "maroon">)</font>
<br/> <font color = "maroon">)</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@Pos</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "silver">+</font> <font color = "red">'%'</font><font color = "silver">,</font> <font color = "#8000FF">@ListOfIDs</font>
<br/> <font color = "maroon">)</font>
<br/> <font color = "green"><i>--SET @Pos = CHARINDEX(@IDsSeperationChar, @ListOfIDs, 1)</i></font>
<br/> <font color = "blue">END</font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">RETURN</font>
<br/> <font color = "blue">END</font>
<br/>
<br/><font color = "maroon">go</font>
</font>
**2nd Step : Code to get the result**
<font face="Courier New" size="2">
<font color = "blue">DECLARE</font> <font color = "#8000FF">@udvMax</font> <font color = "black"><i>NVARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/>
<br/><font color = "blue">SELECT</font> <font color = "#8000FF">@udvMax</font> <font color = "silver">=</font> <font color = "red">''</font> <font color = "silver">+</font> <font color = "fuchsia"><i>Substring</i></font><font color = "maroon">(</font> <font color = "maroon">(</font> <font color = "blue">SELECT</font> <font color = "red">' Union '</font> <font color = "silver">+</font>
<br/> <font color = "red">'Select * from dbo.udfListOfIDs('''</font> <font color = "silver">+</font>
<br/> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font> <font color = "silver">+</font> <font color = "red">''', '''</font> <font color = "silver">+</font> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">id</font> <font color = "silver">+</font> <font color = "red">''', '''</font> <font color = "silver">+</font> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font> <font color = "silver">+</font>
<br/> <font color = "red">''', null,null,null )'</font> <font color = "blue">FROM</font> <font color = "maroon">tmpu</font> <font color = "blue">FOR</font> <font color = "maroon">xml</font> <font color = "maroon">path</font><font color = "maroon">(</font><font color = "red">''</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "silver">,</font> <font color = "black">7</font><font color = "silver">,</font>
<br/> <font color = "black">200000</font><font color = "maroon">)</font>
<br/> <font color = "silver">+</font>
<br/> <font color = "red">' Order by UniqueID1, UniqueID2, UniqueID3, UniqueID4, UniqueID5, ID'</font>
<br/>
<br/><font color = "green"><i>--Select @udvMax</i></font>
<br/><font color = "blue">EXECUTE</font> <font color = "#FF0080"><b>Sp_executesql</b></font>
<br/> <font color = "#8000FF">@udvMax</font>
</font>
****May be u may need to add your criteria in select statement in 2nd step.**
****可能是你可能需要在第二步的select语句中添加你的标准***
Hope this will help you.
希望这能对你有所帮助。
JP
摩根大通
#1
11
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
如果您尝试沿着当前路径:您将不得不拆分输入字符串，因为不能保证每个记录上的代码与输入参数的顺序相同(或连续)。然后你要做a
Code LIKE '%IN%'
AND Code Like '%QA%'
query with an additional statement for every code you are checking for. Very inefficient.
查询为您正在检查的每个代码添加一个额外的语句。效率很低。
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.
下面的UDF想法也是一个好主意。但是，根据数据的大小和查询和更新的频率，您可能也会遇到问题。
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?
是否有可能创建一个额外的表，该表是规范化的，并在计划的基础上(或基于触发器)进行同步，供您查询?
#2
8
First, let's make the original table to become like this:
首先，让我们把原始表格变成这样:
Id | Value
-----+------
0001 | IN
0001 | ME
0001 | OH
0001 | ON
0002 | AC
0002 | ON
0002 | VI
0002 | ZO
0003 | ME
0003 | OO
0003 | PS
0003 | QA
It is accomplished by parsing the comma separated values into rows. Then use the powerful CROSS APPLY keyword to join with the original table to retrieve it's Id. Next step is simply to query this CTE.
它通过将逗号分隔的值解析成行来完成。然后使用强大的CROSS APPLY关键字与原始表连接以检索其Id。下一步就是查询这个CTE。
create function FnSplitToTable
(
@param nvarchar(4000)
)
returns table as
return
with
Num(Pos) as -- list of positions, numbered from 1 to 4000, largest nvarchar
(
select cast(1 as int)
union all
select cast(Pos + 1 as int) from Num where Pos < 4000
)
select substring(@Param, Pos,
charindex(',', @Param + ',', Pos) - Pos) as Value
from Num where Pos <= convert(int, len(@Param))
and substring(',' + @Param, Pos, 1) = ','
go
create proc ProcGetProductId
(
@Codes nvarchar(4000)
)
as
with
Src
(
Id,
Code
)
as
(
select '0001', 'IN,ON,ME,OH'
union all
select '0002', 'ON,VI,AC,ZO'
union all
select '0003', 'QA,PS,OO,ME'
),
Parse as
(
select
s.Id,
f.Value
from
Src as s
cross apply
FnSplitToTable(s.Code) as f
)
select distinct
p.Id
from
Parse as p
join
FnSplitToTable(@Codes) as f
on
p.Value = f.Value
option (maxrecursion 4000)
go
exec ProcGetProductId 'IN,ME' -- returns 0001 & 0003
#3
7
Everybody else seems very eager to tell you that you should not do this, although I don't see any explicit explanation for why not.
其他人似乎都很想告诉你，你不应该这样做，尽管我没有看到任何明确的解释。
Apart from breaking the normalization rules, the reason is that you'll do a table-scan through all rows, since you can't have an index on the individual "values" in that column.
除了打破规范化规则之外，原因是您将对所有行执行表扫描，因为您不能在该列中对单个“值”进行索引。
Simply put, there's no way for the database engine to keep some kind of quick-list of which rows contains the code 'AC', unless you either break it up into a separate table, or put it in a column by itself.
简单地说，数据库引擎不可能保持某种包含代码“AC”的快速列表，除非您将其分解为单独的表，或者将其单独放在列中。
Now, if you have other criteria in your SELECT statements that will limit the number of rows down to some manageable number, then perhaps this will be ok, but otherwise I would, if you can, try to avoid this solution and do what others have already told you, split it up into a separate table.
现在,如果您有其他条件选择语句将限制一些可控的号码记下来的行数,那么也许这将是好的,但否则我会,如果可以,尽量避免这种解决方案和做别人已经告诉你,把它分成一个单独的表中。
Now, if you're stuck with this design, you can do a search using the following type of query:
现在，如果你坚持使用这种设计，你可以使用以下类型的查询进行搜索:
...
WHERE ',' + Code + ',' LIKE '%,AC,%'
This will:
这将:
- Match 'ON,VI,AC,ZO'
- 匹配“VI,AC,佐薇”
- Not match 'ON,VI,TAC,ZO'
- 不匹配,VI,TAC,佐薇”
I don't know if the last one is a viable option in your case, if you only have 2-letter codes, then you can use just this:
我不知道最后一个在你的情况下是否是可行的选择，如果你只有两个字母的编码，那么你可以用这个:
...
WHERE Code LIKE '%AC%'
But again, this will perform horribly unless you limit the number of rows using other criteria.
但是，如果不限制使用其他标准的行数，这将会非常糟糕。
#4
5
Although all the previous posters are correct about the normalization of your db schema, you can do what you want using a "Table-Valued UDF" that takes a delimited string and returns a Table, with one row per value in the string... You can use this table as you would any other table in your stored proc , joining to it, etc... this will solve your immediate issue...
虽然前面所有的海报都是关于db模式的规范化的，但是您可以使用“表值UDF”来实现您想要的操作，该“表值UDF”接受带分隔符的字符串并返回一个表，字符串中每个值有一行……您可以像使用存储proc中的任何其他表一样使用这个表，连接到它，等等……这将解决你眼前的问题……
Here's a link to such a UDF: FN_Split UDF
这里有这样一个UDF的链接:FN_Split UDF
Although the article talks about using it to pass a delimited list of data values in to a stored proc, you can use the same UDF to operate on a delimited string stored in a column of an existing table....
尽管这篇文章谈到了使用它通过分隔的列表数据值存储过程,您可以使用相同的UDF操作一个分隔的字符串存储在现有表的一个列....
#5
4
The way you are storing data breaks normalization rules. Only a single atomic value should be stored in each field. You should store each item in a single row.
存储数据的方式违反了规范化规则。每个字段中应该只存储一个原子值。您应该将每个项目存储在一行中。
#6
4
More than 1 year old question, but still thought it will be useful. You can use the FIND_IN_SET function of MySql. I am not sure whether other DBMSs support it or not.
一个多岁的问题，但还是觉得很有用。可以使用MySql的FIND_IN_SET函数。我不确定其他dbms是否支持它。
You can use this function as follows:
您可以使用以下功能:
SELECT * FROM `table_name` WHERE FIND_IN_SET('AC', `Code`) > 0
#7
0
This might not be possible if you're stuck with that database design, but it would be a lot easier to put the codes into separate records in another table:
如果您坚持使用该数据库设计，这可能是不可能的，但是将代码放在另一个表的单独记录中要容易得多:
ProductCode
-----------
ProductID (FK to Product.ID)
Code (varchar)
The table might look like this:
这个表格可能是这样的:
ProductID Code
-----------------
0001 IN
0001 ON
0001 ME
...
The query would look something like this (you'd have to pass in the Codes somehow - either as separate variables, or maybe a comma-separated string that you split in the proc):
查询看起来是这样的(您必须以某种方式传入代码——或者作为单独的变量，或者作为您在proc中分割的逗号分隔的字符串):
select ProductID
from ProductCode
where Code in ('ON', 'ME')
#8
0
I agree with other posters here that you should look carefully into schema normalization, but I also know that shortcuts are part of life.
我同意这里的其他海报，您应该仔细研究模式规范化，但是我也知道快捷方式是生活的一部分。
Here's a sample function written in Sybase dialect that does what you do:
这里有一个用Sybase方言写的函数，它可以做你想做的事情:
ALTER FUNCTION "DBA"."f_IsInStringList"( IN @thisItem char(2), IN @thisList varchar(4000) )
RETURNS INTEGER
DETERMINISTIC
BEGIN
DECLARE is_member bit;
DECLARE LOCAL TEMPORARY TABLE tmp (thisItem char(2)) ;
DECLARE @tempstring varchar(10);
DECLARE @count integer;
IF LENGTH(TRIM(@thisList)) > 0 THEN
WHILE LENGTH(TRIM(@thisList)) > 0 LOOP
-- loop over comma-separated list and stuff members into temp table
IF LOCATE ( @thisList, ',' , 1) > 0 THEN
SET @count = LOCATE ( @thisList, ',' , 1);
SET @tempstring = SUBSTRING ( @thisList, 1,@count-1 );
INSERT INTO tmp ( thisItem ) VALUES ( @tempstring );
SET @thisList = STUFF ( @thisList, 1, @count, '' )
ELSE
INSERT INTO tmp ( thisItem ) VALUES ( @thisList );
SET @thisList = NULL;
END IF;
END LOOP ;
END IF;
IF EXISTS (SELECT * FROM tmp WHERE thisItem = @thisItem ) THEN
SET is_member = 1;
ELSE
SET is_member = 0 ;
END IF ;
RETURN is_member;
END
You can then build a simple query to check whether a value occurs in your comma-separated string:
然后，您可以构建一个简单的查询来检查值是否出现在逗号分隔的字符串中:
select * from some_table t
WHERE f_IsInStringList('OR', t.your_comma_separated_column) = 1 OR
f_IsInStringList('ME', t.your_comma_separated_column) = 1
#9
0
Although in your case simple LIKE will work, here is the solution how to parse comma separated strings Table Normalization (Parse comma separated fields into individual records).
虽然在您的例子中，简单的LIKE可以工作，但是这里提供了如何解析逗号分隔的字符串表规范化(将逗号分隔的字段解析为单独的记录)的解决方案。
#10
0
if you want to do it with php and mysql it can be any number of keyword no restriction
如果你想用php和mysql来做，可以是任意数量的没有限制的关键字
$var = explode(',',"ahmad,sayeed,asmal,babu");
$query = "SELECT * FROM post WHERE post_tags LIKE '%a%' ";
$query1=NULL;
foreach($var as $value)
{
$query1.= " OR post_tags LIKE '%$value%' ";
}
echo "$query $query1";
OUTPUT:
输出:
SELECT * FROM post WHERE post_tags LIKE '%a%' OR post_tags LIKE '%ahmad%' OR post_tags LIKE '%sayeed%' OR post_tags LIKE '%asmal%' OR post_tags LIKE '%babu%'
从post_tags(比如%a%)或post_tags(如%sayeed%)或post_tags(如%sayeed%)或post_tags(如“%asmal%”)或post_tags(比如“%asmal%”)或post_tags(如“%asmal%”)中选择*。
#11
0
1st Step : Code to create function
第一步:代码创建函数
<font face="Courier New" size="2">
<font color = "blue">CREATE</font> <font color = "blue">FUNCTION</font> <font color = "maroon">[dbo]</font><font color = "silver">.</font><font color = "#FF0080"><b>[Udflistofids]</b></font> <font color = "maroon">(</font>
<br/><font color = "green"><i>-- Add the parameters for the function here</i></font>
<br/><font color = "#8000FF">@ListOfIDs</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/><font color = "green"><i>--, @IDsSeperationChar as varchar(5) = ','</i></font>
<br/><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID1</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID2</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID3</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID4</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID5</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "maroon">returns</font> <font color = "#8000FF">@TabListOfIDs</font> <font color = "blue">TABLE</font> <font color = "maroon">(</font>
<br/> <font color = "green"><i>-- Add the column definitions for the TABLE variable here</i></font>
<br/> <font color = "maroon">id</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid1</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid2</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid3</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid4</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid5</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "blue">AS</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "green"><i>-- Fill the table variable with the rows for your result set</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@Pos</font> <font color = "blue">AS</font> <font color = "black"><i>INT</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ID</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">5</font><font color = "maroon">)</font> <font color = "silver">=</font> <font color = "red">','</font>
<br/>
<br/> <font color = "green"><i>--SET @ListOfIDs = REPLACE( @ListOfIDs, @IDsSeperationChar, ',')</i></font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ListOfIDs</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@Pos</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "silver">+</font> <font color = "red">'%'</font><font color = "silver">,</font> <font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font>
<br/>
<br/> <font color = "green"><i>--SET @Pos = CHARINDEX(@IDsSeperationChar, @ListOfIDs, 1)</i></font>
<br/> <font color = "blue">IF</font> <font color = "fuchsia"><i>Replace</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "#8000FF">@IDsSeperationChar</font><font color = "silver">,</font> <font color = "red">''</font><font color = "maroon">)</font> <font color = "silver"><></font> <font color = "red">''</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">WHILE</font> <font color = "#8000FF">@Pos</font> <font color = "silver">></font> <font color = "black">0</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ID</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>LEFT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "#8000FF">@Pos</font> <font color = "silver">-</font> <font color = "black">1</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/>
<br/> <font color = "blue">IF</font> <font color = "#8000FF">@ID</font> <font color = "silver"><></font> <font color = "red">''</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">INSERT</font> <font color = "blue">INTO</font> <font color = "#8000FF">@TabListOfIDs</font>
<br/> <font color = "maroon">(</font><font color = "maroon">id</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid1</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid2</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid3</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid4</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid5</font><font color = "maroon">)</font>
<br/> <font color = "blue">VALUES</font> <font color = "maroon">(</font><font color = "#8000FF">@ID</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID1</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID2</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID3</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID4</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID5</font><font color = "maroon">)</font> <font color = "green"><i>--Use Appropriate conversion</i></font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ListOfIDs</font> <font color = "silver">=</font> <font color = "fuchsia"><i>RIGHT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font> <font color = "silver">-</font>
<br/> <font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ID</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font><font color = "maroon">)</font>
<br/> <font color = "maroon">)</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@Pos</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "silver">+</font> <font color = "red">'%'</font><font color = "silver">,</font> <font color = "#8000FF">@ListOfIDs</font>
<br/> <font color = "maroon">)</font>
<br/> <font color = "green"><i>--SET @Pos = CHARINDEX(@IDsSeperationChar, @ListOfIDs, 1)</i></font>
<br/> <font color = "blue">END</font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">RETURN</font>
<br/> <font color = "blue">END</font>
<br/>
<br/><font color = "maroon">go</font>
</font>
**2nd Step : Code to get the result**
<font face="Courier New" size="2">
<font color = "blue">DECLARE</font> <font color = "#8000FF">@udvMax</font> <font color = "black"><i>NVARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/>
<br/><font color = "blue">SELECT</font> <font color = "#8000FF">@udvMax</font> <font color = "silver">=</font> <font color = "red">''</font> <font color = "silver">+</font> <font color = "fuchsia"><i>Substring</i></font><font color = "maroon">(</font> <font color = "maroon">(</font> <font color = "blue">SELECT</font> <font color = "red">' Union '</font> <font color = "silver">+</font>
<br/> <font color = "red">'Select * from dbo.udfListOfIDs('''</font> <font color = "silver">+</font>
<br/> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font> <font color = "silver">+</font> <font color = "red">''', '''</font> <font color = "silver">+</font> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">id</font> <font color = "silver">+</font> <font color = "red">''', '''</font> <font color = "silver">+</font> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font> <font color = "silver">+</font>
<br/> <font color = "red">''', null,null,null )'</font> <font color = "blue">FROM</font> <font color = "maroon">tmpu</font> <font color = "blue">FOR</font> <font color = "maroon">xml</font> <font color = "maroon">path</font><font color = "maroon">(</font><font color = "red">''</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "silver">,</font> <font color = "black">7</font><font color = "silver">,</font>
<br/> <font color = "black">200000</font><font color = "maroon">)</font>
<br/> <font color = "silver">+</font>
<br/> <font color = "red">' Order by UniqueID1, UniqueID2, UniqueID3, UniqueID4, UniqueID5, ID'</font>
<br/>
<br/><font color = "green"><i>--Select @udvMax</i></font>
<br/><font color = "blue">EXECUTE</font> <font color = "#FF0080"><b>Sp_executesql</b></font>
<br/> <font color = "#8000FF">@udvMax</font>
</font>
****May be u may need to add your criteria in select statement in 2nd step.**
****可能是你可能需要在第二步的select语句中添加你的标准***
Hope this will help you.
希望这能对你有所帮助。
JP
摩根大通