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

1

Advertisements

11 thoughts on “Extracting numbers from the text in Sqlserver.

  1. Have you ever considered about including a little bit more than just your articles?
    I mean, what you say is important and all. Nevertheless think about if you added some
    great graphics or video clips to give your posts more, “pop”!
    Your content is excellent but with images and video clips, this site could definitely be one of
    the very best in its niche. Terrific blog!

    Like

  2. Hey There. I discovered your blog the usage of msn. This is an
    extremely smartly written article. I will make sure to bookmark it and return to
    read extra of your helpful information. Thanks for the post.
    I’ll certainly return.

    Like

  3. Hello there, I found your site by way of Google whilst searching
    for a similar matter, your web site got here up, it seems great.
    I have bookmarked it in my google bookmarks.
    Hello there, just changed into aware of your blog via Google, and located
    that it is truly informative. I am gonna watch out for brussels.
    I will be grateful should you continue this in future.
    Numerous other folks might be benefited from your writing.
    Cheers!

    Like

  4. Hello there, You have done an excellent job. I will definitely digg it
    and personally suggest to my friends. I’m sure they will be benefited from this website.

    Like

  5. Thanks for your personal marvelous posting! I definitely
    enjoyed reading it, you will be a great author. I will be sure to bookmark your blog and will eventually come back at some point.
    I want to encourage you to ultimately continue your great work,
    have a nice day!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s