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

来源：互联网发布：截面数据的例子编辑：程序博客网时间：2024/06/06 06:58

GOALTER FUNCTION [dbo].[clear_num](@s VARCHAR(100))RETURNs VARCHAR(100) as BEGINWHILE PATINDEX('%[^0-9.]%', @s)>=1BEGIN set @s=replace(@s,SUBSTRING(@s,PATINDEX('%[^0-9.]%', @s),1),'');ENDRETURN(@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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[get_num]ASbegin 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_fFETCH NEXT from cur_f into @name,@id,@first_name;WHILE @@fetch_status=0BEGINset @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

结果如下：（成功）

0 0