if isnumeric(substring(@X,1,charindex(' ',@X)-1)) =1 and isnumeric(substring(reverse(@X),1,charindex(' ',reverse(@X))-1)) =1 set @X = substring(@X,charindex(' ',@X)+1,len(@X)-charindex(' ',@X)-charindex(' ',reverse(@X))+1)加个条件?
一个语句是搞不定的!首先你必须拆分!我先提供给你一个拆分函数,拆分开来,你再做下一步操作!其实你这么复杂的逻辑就不要改放在数据库操作! CREATE function [dbo].[StringSplit](@str nvarchar(max),@spliter nvarchar(10)) returns @tb table(ch nvarchar(256)) AS BEGIN DECLARE @Num int,@Pos int, @NextPos int SET @Num = 0 SET @Pos = 1 WHILE(@Pos <= LEN(@str)) BEGIN SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@str) + 1 INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos)))) SELECT @Pos = @NextPos+1 END return END
create function ufun_IstheStrtheNumber ( @input varchar(4000) ) returns int begin select @input=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@input,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') return len(@input) end --================================================ create function ufun_GetNewStr ( @input varchar(4000) ) returns varchar(4000) begin declare @output varchar(4000) declare @result varchar(4000) declare @subChar varchar(4000) declare @subCharNoLen int declare @i int select @output=ltrim(@input)+' ',@result='',@i=0 while @i<len(ltrim(@input)) begin select @output=ltrim(@output) select @subChar=left(ltrim(@output),charindex(' ',ltrim(@output),1)-1),@i=@i+charindex(' ',ltrim(@output),1) select @subCharNoLen=dbo.ufun_IstheStrtheNumber(@subChar) if @subCharNoLen>1 begin select @result=@result + @subChar +' ' end select @output=replace(@output,@subChar,'') end return @result end --===================================================================select dbo.ufun_GetNewStr('777 aa, bcd5 78')--Resultaa, bcd5
如果输入了多次数字呢?
例如: 1 12 123 abc b5 --> abc b5
反例
01 abc de => abc
反例中最后是字母,应该不处理的呢
CREATE function [dbo].[StringSplit](@str nvarchar(max),@spliter nvarchar(10))
returns @tb table(ch nvarchar(256))
AS
BEGIN
DECLARE @Num int,@Pos int, @NextPos int SET @Num = 0 SET @Pos = 1 WHILE(@Pos <= LEN(@str)) BEGIN SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@str) + 1 INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos)))) SELECT @Pos = @NextPos+1 END return END
create function ufun_IstheStrtheNumber
(
@input varchar(4000)
)
returns int
begin
select @input=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@input,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')
return len(@input)
end --================================================
create function ufun_GetNewStr
(
@input varchar(4000)
)
returns varchar(4000)
begin
declare @output varchar(4000)
declare @result varchar(4000)
declare @subChar varchar(4000)
declare @subCharNoLen int
declare @i int
select @output=ltrim(@input)+' ',@result='',@i=0
while @i<len(ltrim(@input))
begin
select @output=ltrim(@output)
select @subChar=left(ltrim(@output),charindex(' ',ltrim(@output),1)-1),@i=@i+charindex(' ',ltrim(@output),1)
select @subCharNoLen=dbo.ufun_IstheStrtheNumber(@subChar)
if @subCharNoLen>1
begin
select @result=@result + @subChar +' '
end
select @output=replace(@output,@subChar,'')
end
return @result
end
--===================================================================select dbo.ufun_GetNewStr('777 aa, bcd5 78')--Resultaa, bcd5