-- Test Data: tb If object_id('tb') is not null Drop table tb Go Create table tb(f2 nvarchar(20),f3 nvarchar(20) ) Go Insert into tb select 'adf23423423423swrsdf','123456788901adadf' Insert into tb select 'adf234234423swrsdf','123456788901adadf' Insert into tb select 'adf23423423423swrsdf','12345678adadf' go create function f_findnum(@s varchar(20)) returns varchar(11) as begin declare @i int set @i = len(@s) while (@i >0) begin if isnumeric(substring(@s,@i,11)) = 1 return substring(@s,@i,11) set @I = @I - 1 end return 'NULL' end goselect f2,dbo.f_findnum(f2),f3,dbo.f_findnum(f3) from tb drop function f_findnum --Result: /*f2 f3 -------------------- ----------- -------------------- ----------- adf23423423423swrsdf 23423423423 123456788901adadf 23456788901 adf234234423swrsdf NULL 123456788901adadf 23456788901 adf23423423423swrsdf 23423423423 12345678adadf NULL(所影响的行数为 3 行)*/ --End
--提取数字 IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO 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 --测试 PRINT DBO.GET_NUMBER('呵呵ABC123ABC') GO --123
If object_id('tb') is not null
Drop table tb
Go
Create table tb(f2 nvarchar(20),f3 nvarchar(20) )
Go
Insert into tb select 'adf23423423423swrsdf','123456788901adadf'
Insert into tb select 'adf234234423swrsdf','123456788901adadf'
Insert into tb select 'adf23423423423swrsdf','12345678adadf'
go
create function f_findnum(@s varchar(20))
returns varchar(11)
as
begin
declare @i int
set @i = len(@s)
while (@i >0)
begin
if isnumeric(substring(@s,@i,11)) = 1
return substring(@s,@i,11)
set @I = @I - 1
end
return 'NULL'
end
goselect f2,dbo.f_findnum(f2),f3,dbo.f_findnum(f3)
from tb
drop function f_findnum
--Result:
/*f2 f3
-------------------- ----------- -------------------- -----------
adf23423423423swrsdf 23423423423 123456788901adadf 23456788901
adf234234423swrsdf NULL 123456788901adadf 23456788901
adf23423423423swrsdf 23423423423 12345678adadf NULL(所影响的行数为 3 行)*/
--End
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
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
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
http://faq.csdn.net/read/28024.html
http://www.cnblogs.com/lizhiwen/archive/2007/05/23/757477.html
http://www.chenjiliang.com/Article/View.aspx?ArticleID=4247&TypeID=60