Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Extracting numbers - Part 2

Apr 22 2010 10:08AM by Madhivanan   

In this post Extract only numbers from a String,
I described a method of extrating numbers from a string using split and concatenate technique

In this post Regular expression,
I described a method to extract whole numbers if a specific digit is found

Consider that you have a situation where you need to extract only amounts from a text

Here is a way to do it Consider this example

declare @sample table
(
        data varchar(200) 
)

insert @sample

select 'The amount is $87623.63' union all 
select '$87.21 is paid' union all 
select 'you have credit of 1000 US Dollors thanks' 

 select 
        data,
		substring
		(
			data,
			patindex('%[0-9]%',data),
			datalength(data)-
			patindex('%[0-9]%', reverse(data))-
			patindex('%[0-9]%',data)+2
		) as amount
    from 
		@sample

    where 
        data like '%[0-9]%'

The Output is

data                                              amount
----------------------------------             ---------------
The amount is $87623.63                           87623.63
$87.21 is paid                                    87.21
you have credit of 1000 US Dollors thanks         1000

Tags: t-sql, sql_server, extract_number, parsing, regular_expression,


Madhivanan
3 · 40% · 12968
3
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

2  Comments  

  • Hi Madhivanan,

    can you please elaborate on why 2 is being added?

    Thanks, Sreelekha

    commented on Dec 25 2011 12:47AM
    SreelekhaVikram
    1351 · 0% · 17
  • Sreelekha, The code finds the position of a number from both end (lef and right) so 2 should be added to include those digits

    commented on Jun 29 2012 1:21AM
    Madhivanan
    3 · 40% · 12968

Your Comment


Sign Up or Login to post a comment.

"Extracting numbers - Part 2" rated 5 out of 5 by 3 readers
Extracting numbers - Part 2 , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]