如题;
现在的情况是取不到字符串最后一个
exec getKeywords_test 'sasqe,dcxz,sa,qwewe'
CREATE PROCEDURE [dbo].getKeywords_test
(
@key varchar(200)
)
as
declare @currPos int
declare @sql varchar(1000)
declare @TId varchar(200)
set @sql=''
set @currPos = CHARINDEX(',',@key)if @key <>''
begin
if @currPos > 0
begin
while @currPos >0
begin
set @TId = SUBSTRING(@key,0,@currPos)
set @key = REPLACE(@key,@TId+',','')
set @currPos = CHARINDEX(',',@key)
set @sql = @sql + ('keys like ''%'+@TId+'%'' or ')
end
set @sql = SUBSTRING(@sql,0,len(@sql)-2)
select @sql
end
else
begin
select 'bb'
end
end
GO最后的字符串无法取到,请教高手
现在的情况是取不到字符串最后一个
exec getKeywords_test 'sasqe,dcxz,sa,qwewe'
CREATE PROCEDURE [dbo].getKeywords_test
(
@key varchar(200)
)
as
declare @currPos int
declare @sql varchar(1000)
declare @TId varchar(200)
set @sql=''
set @currPos = CHARINDEX(',',@key)if @key <>''
begin
if @currPos > 0
begin
while @currPos >0
begin
set @TId = SUBSTRING(@key,0,@currPos)
set @key = REPLACE(@key,@TId+',','')
set @currPos = CHARINDEX(',',@key)
set @sql = @sql + ('keys like ''%'+@TId+'%'' or ')
end
set @sql = SUBSTRING(@sql,0,len(@sql)-2)
select @sql
end
else
begin
select 'bb'
end
end
GO最后的字符串无法取到,请教高手
建议这样改:
1。传参数时在最后也加上个逗号,变成“aa,bb,cc,”的样子,这样不用改你的算法;
2。
if @key <>''
begin
if @currPos > 0
begin
while @currPos >0
begin
set @TId = SUBSTRING(@key,0,@currPos)
set @key = REPLACE(@key,@TId+',','')
set @currPos = CHARINDEX(',',@key)
set @sql = @sql + ('keys like ''%'+@TId+'%'' or ')
end
set @sql = @sql + @key <---------这里改成这样,因为按你的算法,当@currPos=0的时候,@key的值就是最后一个,你直接加上,也不用去掉最后的“or”了
select @sql
end
else
begin
select 'bb'
end
end
(
@key varchar(200)
)
as
declare @currPos int
declare @sql varchar(1000)
declare @TId varchar(200)
set @sql=''
set @currPos = CHARINDEX(',',@key) --@currPos=6if @key <>''
begin
if @currPos > 0
begin
while @currPos >0
begin
set @TId = SUBSTRING(@key,0,@currPos) --【1】@TId=sasqe 【2】@TId=dcxz 【3】@TId=sa
set @key = REPLACE(@key,@TId+',','')--【1】@key=dcxz,sa,qwewe 【2】@key=sa,qwewe 【3】@key=qwewe
set @currPos = CHARINDEX(',',@key)--【1】@currPos=5 【2】@currPos=3 【3】@currPos=0 下一次跳到--@currPos=0 情况
set @sql = @sql + ('keys like ''%'+@TId+'%'' or ') --【1】@sql=keys like '%sqsqe%' or 【2】keys like '%sasqe%' or keys like '%dcxz%' or 【3】keys like '%sasqe%' or keys like '%dcxz%' or keys like '%sa%'
end
---------------------------------------------------------------------------
--@currPos=0 情况
begin
set @sql = @sql+('keys like ''%'+@key+'%''') --修改的部分
select @sql
end
---------------------------------------------------------------------------
end
else
begin
select 'bb'
end
end
GO