Scrubbing Data - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Scrubbing Data

Author

Topic

MASPuros
Starting Member

2 Posts

Posted - 2010-05-11 : 11:44:25

I have a task to remove alpha and symbol characters from a field to only show the numeric part of it. Example: 1234R56#789-A ... I want to remove the R, #, -, and A to show 123456789.

Need help, can't find any other functions besides ISNUMERIC.

Thanks much appreciated!
Mike

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-11 : 12:11:23

Try this:


SELECT REPLACE(REPLACE(REPLACE(REPLACE(field, 'R', ''), '#', ''), '-', ''), 'A', '')
  FROM (SELECT '1234R56#789-A' AS field) AS T

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 12:22:10

[code]create function dbo.setinteger (@s varchar(500))
returns varchar(500) as
begin
declare @new varchar(500)
declare @i int
set @new=''
set @i=1

while @i<=len(@s)
begin
if substring(@s,@i,1) between '0' and '9'
set @new=@new+substring(@s,@i,1)
set @i=@i+1
end

return @new
end
go

select dbo.setinteger('234234as#__df3534ws') as [integer][/code]

MASPuros
Starting Member

2 Posts

Posted - 2010-05-11 : 13:06:21

Thank you for the function it worked perfectly!

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 14:06:52

You are welcome!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-11 : 15:28:23

I find easier to just copy Madhivanan! A search on Extract Numerics gave me this.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136744&SearchTerms=extract,numerics
Jim

declare @string varchar(50)
set @string = '1234R#!!^56#789-A'

select data, substring(numbers,1,patindex('%[A-Z]%',numbers+' ')-1) as new_data from
(
select data,substring(data,patindex('%[0-9]%',data),len(data)+1) as numbers from
(
select @string as data

) as t
) as t

Everyday I learn something that somebody else already knew

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 16:06:56

quote:
Originally posted by jimf

I find easier to just copy Madhivanan! A search on Extract Numerics gave me this.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136744&SearchTerms=extract,numerics
Jim

declare @string varchar(50)
set @string = '1234R#!!^56#789-A'

select data, substring(numbers,1,patindex('%[A-Z]%',numbers+' ')-1) as new_data from
(
select data,substring(data,patindex('%[0-9]%',data),len(data)+1) as numbers from
(
select @string as data

) as t
) as t

Everyday I learn something that somebody else already knew

OP's request is
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources