Removing NonNumeric characters

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)

Removing NonNumeric characters

Author

Topic

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-22 : 08:44:20

What's the best way to parse all non numeric chars from a string.
I am looking to add a column to a query that shows the value in ms from a string of data.. I want the [duration] column to be converted to Int.

SELECT *, duration = substring(pingstring,charindex('time=',pingstring)+5,4)

It's hard to show by pasting the data here but the last part of the string is actually
the [duration] column that the substring is returning. Thanks in advance for any help.
..bob

sample data returned

PingString - duration
Reply from 159.61.135.1: bytes=32 time=80ms TTL=255 80ms
Reply from 159.61.135.1: bytes=32 time=807ms TTL=255 807m
Reply from 159.61.135.1: bytes=32 time=79ms TTL=255 79ms
Reply from 159.61.135.1: bytes=32 time=79ms TTL=255 79ms

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-22 : 09:37:55

[code]-- table var for sample data
declare @Sample table (id int identity(1,1), pingstring varchar(255))

-- insert sample data
insert @Sample(pingstring)
select 'Reply from 159.61.135.1: bytes=32 time=80ms TTL=255 80ms' union all
select 'Reply from 159.61.135.1: bytes=32 time=807ms TTL=255 807m' union all
select 'Reply from 159.61.135.1: bytes=32 time=79ms TTL=255 79ms' union all
select 'Reply from 159.61.135.1: bytes=32 time=79ms TTL=255 79ms'

-- show sample data
select * from @Sample

-- solution
select
id,
pingstring,
case
when charindex('time=',pingstring) > 0 and charindex('ms',pingstring) > 0
then substring(pingstring,(charindex('time=',pingstring)+5),charindex('ms',pingstring)-(charindex('time=',pingstring)+5))
else NULL
end as duration
from @Sample

-- gives
id pingstring duration
1 Reply from 159.61.135.1: bytes=32 time=80ms TTL=255 80ms 80
2 Reply from 159.61.135.1: bytes=32 time=807ms TTL=255 807m 807
3 Reply from 159.61.135.1: bytes=32 time=79ms TTL=255 79ms 79
4 Reply from 159.61.135.1: bytes=32 time=79ms TTL=255 79ms 79[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.