22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE patindex('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
create table gshangju (dwmc nvarchar(20))
insert into gshangju values('200 ')
insert into gshangju values('2 01')
insert into gshangju values(' 205')
insert into gshangju values(' 6 3')
select DBO.GET_NUMBER2(dwmc)
from gshangju
/*
200
201
205
63
*/
提取数字create function [dbo].[trm] (
@str varchar(1000)
)
returns varchar(1000)
as
begin
declare @s varchar(1000)
declare @i int
set @i=1
while @i<= datalength(@str)
begin
if substring(@str,@i,1)<>''
begin
select @s=isnull(@s,'')+substring(@str,@i,1)
end
select @i=@i+1
end
return @s
end
select dbo.trm(' 0 12 3 ')
select dbo.trm('2 01')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0123
(1 行受影响)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
201
(1 行受影响)