sqlserver,一个自定义函数（提取字符串中的数值，包含0-9和‘.’）

GO
ALTER FUNCTION [dbo].[clear_num](@s VARCHAR(100))
RETURNs VARCHAR(100) 
as 
BEGIN
WHILE PATINDEX('%[^0-9.]%', @s)>=1
BEGIN
 set @s=replace(@s,SUBSTRING(@s,PATINDEX('%[^0-9.]%', @s),1),'');
END
RETURN(@s)
END

创建测试表（源表）：                                 

CREATE TABLE [dbo].[test] (
[name] varchar(100) COLLATE Chinese_PRC_CI_AS NULL ,
[id] int NULL ,
[first_name] varchar(255) COLLATE Chinese_PRC_CI_AS NULL 
)
insert into test values('123asd."456fg',1,'ads');
insert into test values('234asd.:s456f',2,'fd');
insert into test values('345asd.,?s456f',3,'hrd');


目标表test_target 与源表test结构一样

创建存储过程，将源表取出放到另一个表里，被修改列被提取

USE [m_test]
GO
/****** Object:  StoredProcedure [dbo].[get_num]    Script Date: 2016/12/23 9:51:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[get_num]
AS
begin 
		DECLARE @name VARCHAR(1000);
		DECLARE @id bigint;
		DECLARE @first_name VARCHAR(100);
		DECLARE cur_f cursor for SELECT name,id,first_name from test;
		open cur_f
		FETCH NEXT from cur_f into @name,@id,@first_name;
		WHILE @@fetch_status=0
		BEGIN
			set @name=dbo.clear_num(@name);
			INSERT into test_target values (@name,@id,@first_name);
			FETCH NEXT from cur_f into @name,@id,@first_name;
		END;
		CLOSE cur_f;
		DEALLOCATE cur_f;
end 

结果如下：（成功）