例子:字符串:"77777LG99999LG888LGuuuLG999"
特定字符为LG
然后截取特定字符之间的字符集合就应该是:LG99999LGLGuuuLG
不是特定的字符集合:77777888999这个LG位置不是固定的。数据库环境是SQL2000 最好写出函数谢谢!!!
特定字符为LG
然后截取特定字符之间的字符集合就应该是:LG99999LGLGuuuLG
不是特定的字符集合:77777888999这个LG位置不是固定的。数据库环境是SQL2000 最好写出函数谢谢!!!
set @a='77777LG99999LG888LGuuuLG999'
set @a=substring(@a,charindex('LG',@a),len(@a))
set @a=reverse(@a)
set @a=substring(@a,charindex('GL',@a),len(@a))
set @a=reverse(@a)
select @a as aa
declare @str nvarchar(100)
set @str='77777LG99999LG888LGuuuLG999'
set @str= substring (@str,CHARINDEX('LG',@str), len(@str)-CHARINDEX('LG',@str)-2- charindex('LG',reverse(@str)))
print @str
LG99999LG888LGuuuLG
(
@s varchar(200),
@c varchar(10)
)as
declare @t varchar(200)
declare @n varchar(200)
declare @i int
declare @j int
set @i = CHARINDEX(@c,@s)
while @i>0
begin
if @i>1
begin
set @n = ISNULL(@n,'')+LEFT(@s,@i-1)
set @s = STUFF(@s,1,@i-1,'')
end
set @j = CHARINDEX(@c,@s,LEN(@c))
if @j>0
begin
set @t = isnull(@t,'')+ LEFT (@s,@j + LEN(@c)-1)
set @s = STUFF(@s,1,@j + LEN(@c)-1,'')
end
set @i = CHARINDEX(@c,@s)
end
if @s<>''
set @n = isnull(@n,'') + @s
select @t as 特定 ,@n as 不是特定
go
exec pr_test7 '77777LG99999LG888LGuuuLG999','LG'--结果
特定 不是特定
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LG99999LGLGuuuLG 77777888999(1 行受影响)
(
@s varchar(200),
@c varchar(10)
)as
declare @t varchar(200)
declare @n varchar(200)
declare @i int
declare @j int
set @i = CHARINDEX(@c,@s)
while @i>0
begin
if @i>1
begin
set @n = ISNULL(@n,'')+LEFT(@s,@i-1)
set @s = STUFF(@s,1,@i-1,'')
end
set @j = CHARINDEX(@c,@s,LEN(@c))
if @j>0
begin
set @t = isnull(@t,'')+ LEFT (@s,@j + LEN(@c)-1)
set @s = STUFF(@s,1,@j + LEN(@c)-1,'')
end
else
begin
set @n = ISNULL(@n,'')+@s
set @s=''
end
set @i = CHARINDEX(@c,@s)
end
if @s<>''
set @n = isnull(@n,'') + @s
select @t as 特定 ,@n as 不是特定
go
exec pr_test7 '77777LG99999LG888LGuuu999','LG'--结果
特定 不是特定
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LG99999LG 77777888LGuuu999(1 行受影响)
declare @a varchar(100)
declare @r varchar(100)
set @a='77777LG99999LG888LGuuuLG999'
set @a=substring(@a,charindex('LG',@a)+2,len(@a))
set @r = 'LG'+substring(@a,0,charindex('LG',@a))+'LG'
set @a=substring(@a,charindex('LG',@a)+2,len(@a))
set @a=substring(@a,charindex('LG',@a)+2,len(@a))
set @r = @r+'LG'+substring(@a,0,charindex('LG',@a))+'LG'
select @r as RESULT
我试了下,发现2个问题:1.如果字符串是'77777LG99999LG888LGuuuLG'就是字符串最后如果是特定字符串结尾的话,就出现错误,变成了LG99999LG888LGuu
2.我还要把不是特定字符串之间内容给组合在一起谢谢啦
create procedure prco_getSplit(@str nvarchar(256),@g nvarchar(10))
as
begin
declare @yes nvarchar(256),@no nvarchar(256)
set @g = 'LG'
while charindex(@g,@str)>0
begin
set @no = isnull(@no,'')+left(@str,charindex(@g,@str)-1)
set @str = right(@str,len(@str)-charindex(@g,@str)-1)
if charindex(@g,@str)>0
begin
set @yes = isnull(@yes,'')+@g+left(@str,charindex(@g,@str)-1)+@g
set @str = right(@str,len(@str)-charindex(@g,@str)-1)
end
else
begin
set @no = @no +@g+@str
end
end
select @yes [特定集合],@no[非特定集合]
enddrop procedure fn_getSplit
exec prco_getSplit '77777LG99999LG888LGuuuLG999','LG'
/*
特定集合 非特定集合
---------------------------------------------------------
LG99999LGLGuuuLG 77777888(1 row(s) affected)*/exec prco_getSplit '77777LG99999LG888LGuuu999','LG'
/*
特定集合 非特定集合
----------------------------
LG99999LG 77777888LGuuu999(1 row(s) affected)
*/
多发一句话,重发create procedure prco_getSplit(@str nvarchar(256),@g nvarchar(10))
as
begin
declare @yes nvarchar(256),@no nvarchar(256)
set @g = 'LG'
while charindex(@g,@str)>0
begin
set @no = isnull(@no,'')+left(@str,charindex(@g,@str)-1)
set @str = right(@str,len(@str)-charindex(@g,@str)-1)
if charindex(@g,@str)>0
begin
set @yes = isnull(@yes,'')+@g+left(@str,charindex(@g,@str)-1)+@g
set @str = right(@str,len(@str)-charindex(@g,@str)-1)
end
else
begin
set @no = @no +@g+@str
end
end
select @yes [特定集合],@no[非特定集合]
endexec prco_getSplit '77777LG99999LG888LGuuuLG999','LG'
/*
特定集合 非特定集合
---------------------------------------------------------
LG99999LGLGuuuLG 77777888(1 row(s) affected)*/exec prco_getSplit '77777LG99999LG888LGuuu999','LG'
/*
特定集合 非特定集合
----------------------------
LG99999LG 77777888LGuuu999(1 row(s) affected)
*/