Madhivanan's TSQL Blog

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


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

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

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (389) · Preferences

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

Tags: t-sql, sql_server, extract_number,

Previous: Script out Procedures and Functions - Part 2
Next: Count number of words in a string

Madhivanan

3 · 39% · 12472

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

4 Comments

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

commented on Jul 19 2011 1:10AM

patelriki13

1915 · 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

2903 · 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% · 32004

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

84 · 2% · 654

Your Comment

Sign Up or Login to post a comment.

+Madhivanan