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 - Part 3

Feb 28 2011 1:01AM by Madhivanan   

I have already posted two posts about extracting numbers from a string

http://beyondrelational.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2010/04/22/extracting-numbers-part-2.aspx

Loana in her blog posted a post about extracting integers from a string. Common table expression is used in the post. Here is an alternate that uses a user defined function.

Sample data

DECLARE @sample TABLE(
                      id INT,    
                      data VARCHAR(200) ) 
INSERT @sample
       SELECT 1, 'bar 1245 foo' UNION ALL
       SELECT 2, 'foo 35234 bar 38383 fgh gaaaaaaaaa 84' UNION ALL
       SELECT 3, 'bar 126831' UNION ALL
       SELECT 4, '72348707 foo' UNION ALL
       SELECT 5, 'foo bar';

Function which splits the data based on the delimiter

create function split(@data varchar(200),@delimiter char(1))
returns @splitting table (id int identity(1,1), data varchar(200))
as
begin

select @delimiter=replace(@delimiter,' ',','), @data=replace(@data,' ',',')
insert into @splitting(data) 
select 
    substring(@data, n, charindex(@delimiter, @data + @delimiter, n ) - n) 
from 
    ( 
        select number as n from master..spt_values
		where type='p' 
    ) numbers 
where 
    substring( @delimiter + @data, n, 1 ) = @delimiter
return
end

SELECT statement that concatenates the numbers

SELECT distinct
      id, STUFF((SELECT 
                       ', ' + sp.data 
                 FROM 
                      @sample samp cross apply dbo.split(data,' ') as sp
				where sp.data not like '%[^0-9]%' and samp.id=t1.id
                 FOR XML PATH('')), 1, 2, '' ) AS numbers 
FROM 
    @sample as t1

The function is used to split the string and the condition used in the SELECT statement where sp.data not like '%[^0-9]%' is used to find only the numbers from the splitted string

Tags: t-sql, sql_server, sqlserver, tsql, BRH, SQL Server, #TSQL, function, #SQLServer,


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



Submit

Your Comment


Sign Up or Login to post a comment.

"Extract only numbers from a string - Part 3" rated 5 out of 5 by 1 readers
Extract only numbers from a string - Part 3 , 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]