how to display data with number only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to display data with number only

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-01-24 : 03:59:27

Let's say my data as following,


myData
---------------------
119888-V
22887890088388
38383889B
d33388hddd888
11778339399
...
...

How to display data with number only?

My expected result,


myData
---------------------
22887890088388
11778339399
...
...

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-01-24 : 04:03:25

I found the answer,

CREATE FUNCTION [dbo].[extractOnlyNumber]
(@s varchar(300))
RETURNS varchar(300)
AS
BEGIN
--declare @Return varchar(300)
--select @return = case @Country
--when 'Argentina' then 'South America'
--when 'Belgium' then 'Europe'
--when 'Brazil' then 'South America'
--when 'Canada' then 'North America'
--when 'Denmark' then 'Europe'
--when 'Finland' then 'Europe'
--when 'France' then 'Europe'
--else 'Unknown'
--end

Declare @result varchar(100)
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

return @result
end

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-01-24 : 04:12:17

Have a look on this

http://weblogs.sqlteam.com/mladenp/archive/2007/10/18/SQL-Server-Filtering-Numeric-data-from-a-character-based-column.aspx

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources