declare @str varchar(500) set @str='1123jasdfo13=-34ibo-0-3' while patindex('%[^0-9]%',@str)>0 begin set @str=stuff(@str,patindex('%[^0-9]%',@str),1,'') end print @str /* 1123133403 */
写个函数create function fn_GetNum( @s varchar(200) ) returns varchar(200) as begin declare @r varchar(200) set @r = '' while len(@s)>0 begin if left(@s,1) between '0' and '9' set @r = @r + left(@s,1) set @s = stuff(@s,1,1,'') end return @r end go--调用 select dbo.fn_GetNum('1123jasdfo13=-34ibo-0-3')--结果 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1123133403(1 行受影响)
create table #t ( s varchar(100) ) insert into #t select 'dsf121dfds212 ' union all select 'er875df212x21 ' union all select '12df5s8s ' union all select '78879dsf552 ' union all select '1dsa215sdf522 ' go alter function aa(@s varchar(100)) returns varchar(100) as begin declare @i int declare @len int declare @str1 varchar(100) set @str1= ' ' set @len=len(@s) set @i=1 while @i <=@len begin if isnumeric(substring(@s,@i,1))> 0 begin set @str1=@str1+substring(@s,@i,1) end else begin set @str1=@str1+'' end set @i=@i+1 end return @str1 end goselect dbo.aa(s) from #t /* 121212 87521221 1258 78879552 1215522 */
declare @s nvarchar(100)='1123jasdfo13=-34ibo-0-3'; declare @counter int=1; declare @value nvarchar(100)=''; while(@counter<=LEN(@s)) begin if(SUBSTRING(@s,@counter,1) like '%[0-9]%') set @value+=SUBSTRING(@s,@counter,1); set @counter+=1; end select @value;
create FUNCTION fn_numeric(@s varchar(2000)) RETURNS varchar(200) AS BEGIN DECLARE @r varchar(200) SET @r ='' WHILE PATINDEX('%[0-9]%',@s)>0 BEGIN SET @r = @r + SubString(@s,PATINDEX('%[0-9|]%', @s), 1) SET @s = Stuff(@s, 1, PATINDEX('%[0-9|]%', @s), '') end RETURN @r END select dbo.fn_numeric('a2h5我j.8 \') /* 258 */
set @str='1123jasdfo13=-34ibo-0-3'
while patindex('%[^0-9]%',@str)>0
begin
set @str=stuff(@str,patindex('%[^0-9]%',@str),1,'')
end
print @str
/*
1123133403
*/
@s varchar(200)
)
returns varchar(200)
as
begin
declare @r varchar(200)
set @r = ''
while len(@s)>0
begin
if left(@s,1) between '0' and '9'
set @r = @r + left(@s,1)
set @s = stuff(@s,1,1,'')
end
return @r
end
go--调用
select dbo.fn_GetNum('1123jasdfo13=-34ibo-0-3')--结果
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1123133403(1 行受影响)
(
s varchar(100)
) insert into #t
select 'dsf121dfds212 ' union all
select 'er875df212x21 ' union all
select '12df5s8s ' union all
select '78879dsf552 ' union all
select '1dsa215sdf522 '
go
alter function aa(@s varchar(100))
returns varchar(100)
as
begin
declare @i int
declare @len int
declare @str1 varchar(100)
set @str1= ' '
set @len=len(@s)
set @i=1
while @i <=@len
begin
if isnumeric(substring(@s,@i,1))> 0
begin
set @str1=@str1+substring(@s,@i,1)
end
else
begin
set @str1=@str1+''
end
set @i=@i+1
end
return @str1
end
goselect dbo.aa(s) from #t /*
121212
87521221
1258
78879552
1215522
*/
declare @counter int=1;
declare @value nvarchar(100)='';
while(@counter<=LEN(@s))
begin
if(SUBSTRING(@s,@counter,1) like '%[0-9]%')
set @value+=SUBSTRING(@s,@counter,1);
set @counter+=1;
end
select @value;
RETURNS varchar(200)
AS
BEGIN
DECLARE @r varchar(200)
SET @r =''
WHILE PATINDEX('%[0-9]%',@s)>0
BEGIN
SET @r = @r + SubString(@s,PATINDEX('%[0-9|]%', @s), 1)
SET @s = Stuff(@s, 1, PATINDEX('%[0-9|]%', @s), '')
end
RETURN @r
END
select dbo.fn_numeric('a2h5我j.8 \') /*
258
*/