Extract numeric values from string

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 2005 Forums

Transact-SQL (2005)

Extract numeric values from string

Author

Topic

Thiyagu_04
Starting Member

37 Posts

Posted - 2009-03-06 : 02:25:03

Hi
I am having values like this i want to extract numeric and decimal values from bellow values
3 RTX
3.4.RTX
3594
3.4ABC
3.4 xyr

Expected Output
3
3.4
3594
3.4
3.4

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-06 : 04:05:34

Use [.0-9] in place of [0-9] here
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-06 : 04:45:07

Try this once



declare @a varchar(40),@start int, @end int ,@number varchar(1024), @sting varchar(1024),@var varchar(1)
select @a = '12ads..235'
SELECT @end = datalength(@a),@start = 1 ,@number = '',@sting = ''
while ( @start <= @end)
BEGIN
SELECT @var = substring(@a,@start,1)
IF isnumeric(@var) = 1 
SET @number = @number+@var
ELSE 
SET @sting = @sting + @var
SELECT @start = @start+1
END
select @number as Number ,@sting as string

xpandre
Posting Yak Master

212 Posts

Posted - 2009-03-06 : 05:00:08

[code]set nocount on

declare @str table (a varchar(max))
declare @t varchar(max)

insert into @str values ('3 RTX')
insert into @str values ('3.4.RTX')
insert into @str values ('3594')
insert into @str values ('3.4ABC')
insert into @str values ('3.41 xyr')

declare c1 cursor for select a from @str
open c1
fetch c1 into @t
while @@fetch_status = 0
begin

select cast(a as decimal(6,2)) from
(
select
case when len(@t) - len(replace(@t,'.','')) > 1 then
reverse(substring
(reverse(
cast(cast((select substring(@t,n,1)
from (select 1 n
union select 2 n
union select 3 n
union select 4 n
union select 5 n
union select 6 n
union select 7 n
union select 8 n
union select 9 n
) as num
where n <= len(@t)
and substring(@t,n,1) not like '[a-z]' for xml path('')) as xml)
as varchar(max))),2,len(@t)))

else
cast(cast((select substring(@t,n,1)
from (select 1 n
union select 2 n
union select 3 n
union select 4 n
union select 5 n
union select 6 n
union select 7 n
union select 8 n
union select 9 n
) as num
where n <= len(@t)
and substring(@t,n,1) not like '[a-z]' for xml path('')) as xml)
as varchar(max))
end a
) f

fetch c1 into @t
end

close c1
deallocate c1

set nocount off[/code]

pootle_flump

1064 Posts

Posted - 2009-03-06 : 05:15:21

Set based solution:

SET NOCOUNT ON

DECLARE @str TABLE (a VARCHAR(MAX))

DECLARE @t VARCHAR(MAX)

INSERT INTO @str VALUES ('3 RTX')
INSERT INTO @str VALUES ('3.4.RTX')
INSERT INTO @str VALUES ('3594')
INSERT INTO @str VALUES ('3.4ABC')
INSERT INTO @str VALUES ('3.41 xyr')

SELECT	SUBSTRING(a, 1, COALESCE(NULLIF(PATINDEX('%[^0-9.]%', a) - CASE WHEN LEN(a) - LEN(REPLACE(a, '.', '')) >= 2 THEN 2 ELSE 1 END, -1), LEN(a)))
FROM    @str

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources