Extracting numbers from the text in Sqlserver.

Input: This is 135 US dollars.

Output: 135

Logic: It is very simple, first of all we find the index of the first numeric character using “patindex” function. Then we find the position of white space and extract sub string that starts from numeric character and ends with white space. But there are few cases like what if number is in the last in the text then we extract sub string till the end of the text.

declare @sample table
(
        data varchar(200)
)

insert @sample

select ‘The amount was $874523.63’ union all
select ‘$6663.025 is already paid’ union all
select ‘You got 500 US Dollors thanks a lot’ UNION ALL
select ‘No numeric character in this line’ union all
select ‘$5567.981’ union all
select ‘you have total 250 US Dollors’

 select
        data,
        substring
        (
            data,
            patindex(‘%[0-9]%’,data),
            CASE WHEN (CHARINDEX(‘ ‘,data,patindex(‘%[0-9]%’,data)) =0 and patindex(‘%[0-9]%’,data) > 0) then LEN(data)
            WHEN patindex(‘%[0-9]%’,data) = 0 THEN 0
            ELSE (CHARINDEX(‘ ‘,data,patindex(‘%[0-9]%’,data)) – patindex(‘%[0-9]%’,data)) end
            
        ) as amount
    from
        @sample