select name from tablename where ','+name+',' like '%a%' OR ','+name+',' like '%c%'
select name from tablename where ','+name+',' like '%,a,%' OR ','+name+',' like '%,c,%'
USE tempdb GO IF OBJECT_ID('t') IS NOT NULL DROP TABLE t GO CREATE TABLE t( [name] VARCHAR(MAX) ) GO INSERT INTO t ([name]) VALUES('a,b,c,d'); INSERT INTO t ([name]) VALUES('a,e'); GO SELECT * FROM t WHERE EXISTS ( SELECT 1 FROM (VALUES('a'),('c')) AS L(v) WHERE ','+ t.[name]+',' LIKE '%,'+L.v+',%' ); /* name a,b,c,d a,e */
--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([name] nvarchar(27)) Insert #T select N'a,b,c,d' union all select N'a,e' union all select N'b,f' Go --测试数据结束 DECLARE @str NVARCHAR(100)='a,c' SELECT * FROM #T WHERE EXISTS ( SELECT * FROM dbo.F_Split(@str, ',') WHERE CHARINDEX(',' + value + ',', ',' + name + ',') > 0 );
CREATE FUNCTION dbo.F_Split ( @SplitString nvarchar(max), --源字符串 @Separator nvarchar(10)=' ' --分隔符号,默认为空格 ) RETURNS @SplitStringsTable TABLE --输出的数据表 ( [id] int identity(1,1), [value] nvarchar(max) ) AS BEGIN DECLARE @CurrentIndex int; DECLARE @NextIndex int; DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1; WHILE(@CurrentIndex<=len(@SplitString)) BEGIN SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0 OR @NextIndex IS NULL) SELECT @NextIndex=len(@SplitString)+1; SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); SELECT @CurrentIndex=@NextIndex+1; END RETURN; END GO
like '%[a,b]%' -- 这样就可以了。
SELECT name FROM table WHERE name LIKE '%a%' or name LIKE '%b%'
Select * from 表名 where 字段名 like '%筛选字符%' or 字段名 like '%筛选字符%
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
[name] VARCHAR(MAX)
)
GO
INSERT INTO t ([name]) VALUES('a,b,c,d');
INSERT INTO t ([name]) VALUES('a,e');
GO
SELECT * FROM t
WHERE EXISTS
(
SELECT 1 FROM (VALUES('a'),('c')) AS L(v)
WHERE ','+ t.[name]+',' LIKE '%,'+L.v+',%'
);
/*
name
a,b,c,d
a,e
*/
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([name] nvarchar(27))
Insert #T
select N'a,b,c,d' union all
select N'a,e' union all
select N'b,f'
Go
--测试数据结束
DECLARE @str NVARCHAR(100)='a,c'
SELECT
*
FROM
#T
WHERE
EXISTS
(
SELECT
*
FROM
dbo.F_Split(@str, ',')
WHERE
CHARINDEX(',' + value + ',', ',' + name + ',') > 0
);
(
@SplitString nvarchar(max), --源字符串
@Separator nvarchar(10)=' ' --分隔符号,默认为空格
)
RETURNS @SplitStringsTable TABLE --输出的数据表
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
GO