DECLARE @keyword NVARCHAR(500)
DECLARE @start INT,@end INT
DECLARE @sql NVARCHAR(1000)
SET @keyword = '软件;硬件;电脑'
SET @sql = ' 1=0 '
SET @start = 1
SET @end = CHARINDEX(';', @keyword, @start)
IF @end = 0 BEGIN SET @end = LEN(@keyword) + 1 END
WHILE(@end > @start)
BEGIN
SET @sql = @sql + ' OR KEYWORD like ''%' + SUBSTRING(@keyword, @start, @end - @start) + '%'''
SET @start = @end + 1
SET @end = CHARINDEX(';', @keyword, @start)
IF @end = 0
BEGIN
SET @end = LEN(@keyword) + 1
END
END
exec ('select * from 表 where ' + @sql)
DECLARE @start INT,@end INT
DECLARE @sql NVARCHAR(1000)
SET @keyword = '软件;硬件;电脑'
SET @sql = ' 1=0 '
SET @start = 1
SET @end = CHARINDEX(';', @keyword, @start)
IF @end = 0 BEGIN SET @end = LEN(@keyword) + 1 END
WHILE(@end > @start)
BEGIN
SET @sql = @sql + ' OR KEYWORD like ''%' + SUBSTRING(@keyword, @start, @end - @start) + '%'''
SET @start = @end + 1
SET @end = CHARINDEX(';', @keyword, @start)
IF @end = 0
BEGIN
SET @end = LEN(@keyword) + 1
END
END
exec ('select * from 表 where ' + @sql)
insert into t_a select '软件;硬件;电脑'
insert into t_a select '软件;手机'
insert into t_a select '软件'
insert into t_a select '手机'if exists(select 1 from sysobjects where id=object_id(N'f_Occur') and xtype='FN')
drop function f_Occur
gocreate function f_Occur(@String varchar(1000),@Separator varchar(2),@DestStr varchar(1000))
returns int
as
begin
declare @i int
set @i=0 while charindex(@Separator,@String)>0
begin
if charindex(rtrim(ltrim(left(@String,charindex(';',@String)-1))),@DestStr)>0
begin
set @i=1
break
end
select @String=stuff(@String,1,charindex(';',@String),'')
end
if charindex(rtrim(ltrim(@String)),@DestStr)>0
begin
set @i=1
end
return(@i)
end
goselect * from t_a a where exists(select 1 from t_a where dbo.f_occur('硬件;手机',';',a.keyword)=1)