/****************************************
--功能:分割字符串,返回字符串记录集
--@Str:要分割的字符串
--@Split:分割字符
--@Dist:是否去除重复项
*****************************************/
if not object_id('GetRecords') is null
drop function GetRecords
go
create function GetRecords(
@str varchar(4000),
@split nvarchar(40),
@Dist bit)
returns @Rec table (Record nvarchar(120))
as
begin
declare @s nvarchar(4000)
declare @r nvarchar(4000)
declare @i int
Declare @Len tinyint
select @Len=len(@Split)
set @s=ltrim(rtrim(@str))
set @i=CHARINDEX(@split,@s)
while @i>0
begin
set @r=left(@s,@i-1)
if @Dist=1
begin
if not exists(Select 1 from @rec where Record=@r)
insert @Rec values (rtrim(@R))
end
else
begin
insert @Rec values (rtrim(@R))
end
set @s=ltrim(right(@s,len(@s)-@i-(@len-1)))
set @i=CHARINDEX(@Split,@s)
end
if len(@s)>0
if @Dist=1
begin
if not exists(Select 1 from @rec where Record=@s)
insert @Rec values (@s)
end
else
begin
insert @Rec values (@s)
end
return
end
go
declare @t table (tx nvarchar(100))
insert into @t(tx)
select '人类'
union all select
'天气,人物'
union all select
'自然,车和房'
union all select
'房子,大自然'
union all select
'大自然真美'
select distinct a.tx from @t a
,dbo.GetRecords('人物,房子,大自然',',',1) b
where charindex(','+b.record+',',','+a.tx+',')>0
if object_id('pubs..tbA') is not null
drop table tbA
go
create table tbA(string varchar(10))
if object_id('pubs..tbB') is not null
drop table tbB
go
create table tbb(id int, string varchar(20))
insert into tbB(id,string) values(1, '人类')
insert into tbB(id,string) values(2, '天气,人物')
insert into tbB(id,string) values(3, '自然,车和房')
insert into tbB(id,string) values(4, '房子,大自然')
insert into tbB(id,string) values(5, '大自然真美')
go
declare @Days varchar(4000)
declare @tmpDay varchar(10)
set @Days='人物,房子,大自然'
set @tmpDay=''
declare @i int
set @i=0
while @i<len(@Days)
begin
set @i=@i+1
if SUBSTRING(@Days,@i,1)=','
begin
insert into tba(string) values( left(@Days,@i-1))
set @Days=SUBSTRING(@Days,@i+1,len(@Days))
set @i=0
end
end
insert into tba(string) values(@Days)
select distinct tbb.* from tbb,tba where charindex(',' + tba.string + ',', ',' + tbb.string + ',') > 0
drop table tbA,tbB
/*
id string
----------- --------------------
2 天气,人物
4 房子,大自然
--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
create table t1(cp int,name varchar(100))
insert t1
select 1,'人类' union all
select 2,'天气,人物' union all
select 3,'自然,车和房' union all
select 4,'房子,大自然' union all
select 5,'大自然真美'
select a.*
from t1 a,(SELECT
dbo.f_splitSTR(userinfo,',', 1) as c1,
dbo.f_splitSTR(userinfo,',', 2) as c2,
dbo.f_splitSTR(userinfo,',', 3) as c3
FROM(
SELECT userinfo = '人物,房子,大自然'
)c ) b
where charindex(','+b.c1+',',','+name+',')>0 or
charindex(','+b.c2+',',','+name+',')>0 or
charindex(','+b.c3+',',','+name+',')>0
--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
insert t1
select 1,'人类' union all
select 2,'天气,人物' union all
select 3,'自然,车和房' union all
select 4,'房子,大自然' union all
select 5,'大自然真美'
select a.*
from t1 a,(SELECT
dbo.f_splitSTR(userinfo,',', 1) as c1,
dbo.f_splitSTR(userinfo,',', 2) as c2,
dbo.f_splitSTR(userinfo,',', 3) as c3
FROM(
SELECT userinfo = '人物,房子,大自然'
)c ) b
where charindex(','+b.c1+',',','+name+',')>0 or
charindex(','+b.c2+',',','+name+',')>0 or
charindex(','+b.c3+',',','+name+',')>0
create table test(keyword nvarchar(100))
insert into test
select '人类' union
select '天气,人物' union
select '自然,车和房' union
select '房子,大自然' union
select '大自然真美'
declare @s nvarchar(100),@sql nvarchar(3000)
set @s = '人物,房子,大自然'
set @sql = ' select keyword from test where '
while charindex(',',@s) <> 0
begin
set @sql = @sql + ' keyword like ''%,' + left(@s,charindex(',',@s) - 1) + '%'' or keyword like ''%'+ left(@s,charindex(',',@s)-1) + ',%'' or'
set @s = substring(@s,charindex(',',@s) + 1 ,(len(@s) - charindex(',',@s)))
end
set @sql = left(@sql,len(@sql) -2)
exec (@sql)
create table tb2(id int, content varchar(100))
insert tb2
select 1,'人类' union all
select 2,'天气,人物' union all
select 3,'自然,车和房' union all
select 4,'房子,大自然' union all
select 5,'大自然真美'
create table temp(aaa varchar(100))
declare @strsql as varchar(8000)
select @strsql=''
select @strsql='insert into temp values('''+replace(''+querytext+'',',',''') insert into temp values (''')+''')' from tb1
exec (@strsql)
select distinct b.* from temp a, tb2 b
where charindex(','+a.aaa+',',','+b.content+',')>0
drop table tb1,tb2,temp
/*
id content
--------------------
2 天气,人物
4 房子,大自然
*/
create table tb2(content varchar(100))
insert tb2
select '人类' union all
select '天气,人物' union all
select '自然,车和房' union all
select '房子,大自然' union all
select '大自然真美'
create table temp(aaa varchar(100))
declare @strsql as varchar(8000)
select @strsql=''
select @strsql='insert into temp values('''+replace(''+querytext+'',',',''') insert into temp values (''')+''')' from tb1
exec (@strsql)
select distinct b.* from temp a, tb2 b
where charindex(','+a.aaa+',',','+b.content+',')>0
drop table tb1,tb2,temp
/*
content
-----------
房子,大自然
天气,人物
*/