捕获sql server执行的命令 sql抓取数据

现需求从上方测试数据的“备注”列中提取出金额

目前有两个方法比较容易实现：

1、首先比较容易想到的就是利用函数stuff删除掉所有的非数值字符。

STUFF ( character_expression , start , length ,character_expression ) 

利用函数stuff，将所有非数值字符全部删除掉，自然就只剩下数值了。

首先需要定位到非数值的字符，用空字符替换掉这些字符，之后通过循环替换掉所有的非数值字符。

这里还需要函数patindex来定位字符串中的非数值字符。

PATINDEX ( '%pattern%' , expression )

自定义一个函数get_number如下：

1 IF OBJECT_ID('dbo.get_number') IS NOT NULL
 2     DROP FUNCTION dbo.get_number;
 3 GO
 4 
 5 create function dbo.get_number(@S nvarchar(1000))
 6 returns int
 7 as
 8 begin
 9     while PATINDEX('%[^0-9]%', @S) > 0
10     begin
11         set @S = STUFF(@S, PATINDEX('%[^0-9]%', @S), 1, '')
12     end
13     return @S
14 end
15 go
16 
17 select dbo.get_number('花费3200元') as result
18 go

 

运行结果如下：

调用该自定义函数完成查询，目标任务实现。

不过这里刚好数值都是整数，如果存在小数的情况，只需把自定义函数get_number中返回变量类型改为float，函数PATINDEX后匹配的字符中增加上“.”就可以了。

1 IF OBJECT_ID('dbo.get_number') IS NOT NULL
 2     DROP FUNCTION dbo.get_number;
 3 GO
 4 
 5 create function dbo.get_number(@S nvarchar(1000))
 6 returns float
 7 as
 8 begin
 9     while PATINDEX('%[^.0-9]%', @S) > 0
10     begin
11         set @S = STUFF(@S, PATINDEX('%[^.0-9]%', @S), 1, '')
12     end
13     return @S
14 end
15 go
16 
17 select dbo.get_number('花费45.02元') as result
18 go

 

2、利用函数substring抽取出字符串中的数值

SUBSTRING      ( expression  ,  start  ,  length) 

根据substring的用法，需要定位出数值开始的位置与结束的位置，并计算出数值的长度。数值开始位置，利用PATINDEX来实现。结束位置的获取，只需将字符串利用函数reverse进行翻转，之后再使用PATINDEX来获取。

 

1 IF OBJECT_ID('dbo.get_number') IS NOT NULL
 2     DROP FUNCTION dbo.get_number;
 3 GO
 4 
 5 create function dbo.get_number(@S nvarchar(1000))
 6 returns int
 7 as
 8 begin
 9     set @S = SUBSTRING(@S,PATINDEX('%[0-9]%', @S),len(@S)-PATINDEX('%[0-9]%', @S)-PATINDEX('%[0-9]%', reverse(@S))+2)
10     return @S
11 end
12 go
13 
14 select dbo.get_number('花费1200元') as result
15 go