由于您的关系是多对多的，所以应将代码存储在单独的表中。如果您将它们分开，则可以轻松地进行检查。
在您现有的系统类型中也可能实现，但需要对列进行文本搜索，并针对每行执行多个搜索，这会导致巨大的性能问题随着数据的增长而出现。
如果您尝试继续当前的路径： 您将不得不拆分输入字符串，因为没有任何保证每个记录上的代码与输入参数相同的顺序（或连续）。然后，您将必须进行
Code LIKE '%IN%'
AND Code Like '%QA%'
对于您正在检查的每个代码，都需要使用一个附加语句进行查询。这样非常低效。
下面的UDF想法也是一个不错的想法。但是，根据您的数据大小以及查询和更新的频率，您可能也会遇到问题。
是否可以创建一个规范化的附加表，并在预定时间（或基于触发器）同步该表，以供您进行查询？
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
这是通过将逗号分隔的值解析为行来完成的。然后使用强大的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
其他人似乎都很热衷于告诉你不应该这样做，尽管我没有看到任何明确的解释为什么不应该这样做。
除了违反规范化规则之外，原因是你将会对所有行进行表扫描，因为你无法在该列的各个“值”上建立索引。
简单来说，数据库引擎无法保持某种快速列表，以指示哪些行包含代码“AC”，除非你将其拆分成一个单独的表，或者将其放在单独的列中。
现在，如果你的SELECT语句中有其他限制行数的条件，那么可能这样做没问题，但否则，如果可以的话，我建议你避免这种解决方案，而是将其拆分成一个单独的表。
现在，如果你被这个设计所困扰，你可以使用以下类型的查询进行搜索：
...
WHERE ',' + Code + ',' LIKE '%,AC,%'
这将会：
如果你只有两个字母的代码，我不确定最后一个选项是否适合你的情况，那么你可以只使用这个：
...
WHERE Code LIKE '%AC%'
但是，除非您使用其他条件限制行数，否则这将表现得非常差。
Code字段的开头和结尾添加了逗号，然后再使用LIKE谓词进行匹配。 - Bill Karwin虽然这是一个超过1年的问题，但我认为它仍然会有用。您可以使用MySql的FIND_IN_SET函数。我不确定其他DBMS是否支持此功能。
您可以按以下方式使用此功能：
SELECT * FROM `table_name` WHERE FIND_IN_SET('AC', `Code`) > 0
第一步：创建函数的代码
<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>
也许在第二步中，您需要在选择语句中添加自己的条件。
希望这可以帮助到您。
JP
如果你被困在那个数据库设计中，这可能是不可能的，但是把代码放到另一个表格的单独记录中会更容易:
ProductCode
-----------
ProductID (FK to Product.ID)
Code (varchar)
表格可能长这样：
ProductID Code
-----------------
0001 IN
0001 ON
0001 ME
...
select ProductID
from ProductCode
where Code in ('ON', 'ME')
我同意其他帖子中的观点，你应该仔细研究模式规范化，但我也知道捷径是生活的一部分。
这里有一个用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
然后，您可以构建一个简单的查询来检查一个值是否出现在您的逗号分隔字符串中：
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
如果你想用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";
输出：
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%'