[SQL] 从文本中提取数值

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

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

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

STUFF ( character_expression , start , length ,character_expression ) 

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

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

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

PATINDEX ( '%pattern%' , expression )

自定义一个函数get_number如下：

IF OBJECT_ID('dbo.get_number') IS NOT NULL
    DROP FUNCTION dbo.get_number;
GO

create function dbo.get_number(@S nvarchar(1000))
returns int
as
begin
    while PATINDEX('%[^0-9]%', @S) > 0
    begin
        set @S = STUFF(@S, PATINDEX('%[^0-9]%', @S), 1, '')
    end
    return @S
end
go

select dbo.get_number('花费3200元') as result
go

 

运行结果如下：

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

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

IF OBJECT_ID('dbo.get_number') IS NOT NULL
    DROP FUNCTION dbo.get_number;
GO

create function dbo.get_number(@S nvarchar(1000))
returns float
as
begin
    while PATINDEX('%[^.0-9]%', @S) > 0
    begin
        set @S = STUFF(@S, PATINDEX('%[^.0-9]%', @S), 1, '')
    end
    return @S
end
go

select dbo.get_number('花费45.02元') as result
go

 

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

SUBSTRING      ( expression  ,  start  ,  length) 

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

 

IF OBJECT_ID('dbo.get_number') IS NOT NULL
    DROP FUNCTION dbo.get_number;
GO

create function dbo.get_number(@S nvarchar(1000))
returns int
as
begin
    set @S = SUBSTRING(@S,PATINDEX('%[0-9]%', @S),len(@S)-PATINDEX('%[0-9]%', @S)-PATINDEX('%[0-9]%', reverse(@S))+2)
    return @S
end
go

select dbo.get_number('花费1200元') as result
go