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

Extract only numbers from a String

Dec 18 2007 2:48PM by Madhivanan   

Sometimes we may need to extract only numbers from a string.

Usually I see people using while loop to check each and every character
to see if it is a number and extract it

Here is a different approach

Declare @s varchar(100),@result varchar(100)
set @s='as4khd0939sdf78' 
set @result=''

select
    @result=@result+
            case when number like '[0-9]' then number else '' end from 
    (
         select substring(@s,number,1) as number from 
        (
            select number from master..spt_values 
            where type='p' and number between 1 and len(@s)
        ) as t
    ) as t 
select @result as only_numbers 

Result

4093978

Tags: t-sql, sql_server, extract_number,


Madhivanan
3 · 40% · 12947
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • nice....thanks.........for different approach without loop...............

    commented on Jul 19 2011 1:10AM
    patelriki13
    2013 · 0% · 9
  • Please will somebody please explain what the below line exactly is doing?

    select number from master..spt_values 
    where type='p`
    
    commented on Jul 22 2011 11:05AM
    getusama
    3073 · 0% · 2
  • @getusama,

    In this example master..spt_values is used as a tally table. When you filter where type="p" you get a result set with a sequence of numbers starting from 0 to over 2000. A tally table helps to solve several TSQL problems in a SET based fashion. So it is a common practice to keep a tally-table or calendar-table in most databases.

    When posting an example in a public forum, most people use master..spt_values to demonstrate the concept if the example needs a tally table. It allows some one reading the post to copy the code and test it right-away (without going through the tally table creation process first).

    commented on Jul 22 2011 10:19PM
    Jacob Sebastian
    1 · 100% · 32235
  • Hi Madhivanan,

    Recently i have read your post regarding Tally Table.So we can replace your part of the code with following code:

    Existing code:

    select substring(@s,number,1) as number from ( select number from master..spt_values where type='p' and number between 1 and len(@s) ) as t

    New Code:

    SELECT SUBSTRING(@s,N,1) FROM TALLY WHERE N<=LEN(@s)

    NOTE: Tally table should exists.

    Thanks for your original post.

    commented on Jun 13 2013 1:06AM
    Bala Krishna
    83 · 2% · 676

Your Comment


Sign Up or Login to post a comment.

"Extract only numbers from a String" rated 5 out of 5 by 1 readers
Extract only numbers from a String , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]