34,594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE (id INT PRIMARY KEY, bh nCHAR(20))
INSERT INTO @t VALUES (1,'01;02;102')
INSERT INTO @t VALUES (2,'01;102')
INSERT INTO @t VALUES (3,'01')
INSERT INTO @t VALUES (4,'02')
--nchar 会自动往右边补空串,要加 rtrim
SELECT * FROM @t AS t WHERE ';'+ rtrim(t.bh) +';' LIKE '%'+';02;'+'%'
/*
id bh
1 01;02;102
4 02
*/
DECLARE @t TABLE (id INT PRIMARY KEY, bh VARCHAR(20))
INSERT INTO @t VALUES (1,'01;02;102')
INSERT INTO @t VALUES (2,'01;102')
INSERT INTO @t VALUES (3,'01;02')
SELECT * FROM @t AS t WHERE ';'+t.bh+';' LIKE '%'+';02;'+'%'
/*
id bh
1 01;02;102
3 01;02
*/
[/quote]DECLARE @t TABLE (id INT PRIMARY KEY, bh VARCHAR(20))
INSERT INTO @t VALUES (1,'01;02;102')
INSERT INTO @t VALUES (2,'01;102')
INSERT INTO @t VALUES (3,'01;02')
SELECT * FROM @t AS t WHERE ';'+t.bh+';' LIKE '%'+';02;'+'%'
/*
id bh
1 01;02;102
3 01;02
*/
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[编号] nvarchar(29))
Insert #T
select 1,N'01;02;102' union all
select 2,N'01;102'
Go
--测试数据结束
DECLARE @str NVARCHAR(100)='02' --相查的数据
SELECT *
FROM #T
WHERE CHARINDEX(';' + @str + ';', ';' + 编号 + ';') > 0
if object_id('tempdb..#Tmp_Data') is not null
drop table #Tmp_Data
CREATE TABLE #Tmp_Data (
id int ,
code varchar(50))
INSERT INTO #Tmp_Data
Select 1,'01;02;102' union all
Select 2,'01;102'
UPDATE #Tmp_Data SET code=';'+code+';'
Select * From #Tmp_Data Where Charindex(';02;',code)>0
UPDATE #Tmp_Data SET code=substring(code,2,len(code)-2)
id code
----------- --------------------------------------------------
1 ;01;02;102;
DECLARE @t TABLE (id INT PRIMARY KEY, bh VARCHAR(20))
INSERT INTO @t VALUES (1,'01;02;102')
INSERT INTO @t VALUES (2,'01;102')
SELECT * FROM @t AS t WHERE ';'+t.bh+';' LIKE '%'+';02;'+'%'
/*
id bh
1 01;02;102
*/