create function [dbo].[f_split](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(200)) as begin while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) return end SELECT * from f_split('1 ¦2 ¦11 ¦12 ¦15 ¦25','¦')
DECLARE @a varchar(200) SET @a='2'--获取值 --set @a='1' SELECT * from f_split('1 ¦2 ¦11 ¦12 ¦15 ¦25','¦') WHERE col LIKE '%'+@a+'%'
没太看懂楼主的要求,写个效率低的select distinct * from table where (col like '%1%') or (col like '%2%') or (col like '%11%') ......
1.写存储过程(推荐)2.写sql的话直接用like不行的。(col like '%1%') 会把11也搜出来 应该先在头和尾加上'|',然后再用like '%|要找的值|%'
create function [dbo].[f_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end SELECT * from f_split('1 ¦2 ¦11 ¦12 ¦15 ¦25','¦')
DECLARE @a varchar(200)
SET @a='2'--获取值
--set @a='1'
SELECT * from f_split('1 ¦2 ¦11 ¦12 ¦15 ¦25','¦') WHERE col LIKE '%'+@a+'%'
distinct *
from
table
where
(col like '%1%')
or (col like '%2%')
or (col like '%11%')
......
应该先在头和尾加上'|',然后再用like '%|要找的值|%'