Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Replace non numeric characters in string Expand / Collapse
Author
Message
Posted Monday, March 17, 2008 11:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:18 AM
Points: 150, Visits: 803
Hi,

I know the following code will replace single defined character (*)

REPLACE(Phone, '*', '')

but I need to replace (remove) any non-numeric character found within a string. Can anyone help please?

Thanks in advance,

Neal
Post #470379
Posted Monday, March 17, 2008 12:29 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
USE [Common]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[AlphaRemove]
(@String_in varchar(max))
returns varchar(max)
as
begin
/*
***** Takes a string variable and turns it into a set of
***** numbers separated by spaces.

***** Despite the name, it also removes punctuation, not
***** just letters.

***** Input string must be simple ASCII, not Unicode.
***** (No accented letters, etc.)
*/
declare @sub char(1)

--Letters
while patindex('%[a-z]%', @string_in) > 0
begin
set @sub = substring(@string_in, patindex('%[a-z]%', @string_in), 1)

set @string_in = replace(@string_in, @sub, '')
end

--Punctuation
while patindex('%[!-)]%', @string_in) > 0
begin
set @sub = substring(@string_in, patindex('%[!-/]%', @string_in), 1)

set @string_in = replace(@string_in, @sub, '')
end

while patindex('%[+-/]%', @string_in) > 0
begin
set @sub = substring(@string_in, patindex('%[!-/]%', @string_in), 1)

set @string_in = replace(@string_in, @sub, '')
end

while patindex('%[:-=]%', @string_in) > 0
begin
set @sub = substring(@string_in, patindex('%[:-@]%', @string_in), 1)

set @string_in = replace(@string_in, @sub, '')
end

while patindex('%[?-@]%', @string_in) > 0
begin
set @sub = substring(@string_in, patindex('%[:-@]%', @string_in), 1)

set @string_in = replace(@string_in, @sub, '')
end

set @string_in = replace(@string_in, '[', '')

while patindex('%[\-`]%', @string_in) > 0
begin
set @sub = substring(@string_in, patindex('%[\-`]%', @string_in), 1)

set @string_in = replace(@string_in, @sub, '')
end

while patindex('%[{-~]%', @string_in) > 0
begin
set @sub = substring(@string_in, patindex('%[{-~]%', @string_in), 1)

set @string_in = replace(@string_in, @sub, '')
end

while charindex(' ', @string_in, 0) > 0
set @string_in = replace(@string_in, ' ', ' ')

return @string_in
end

It's pretty RBAR, but it does work. One of these days, I'll rewrite it to be set-based. (Originally wrote this thing years ago. Was clueless on SQL.)


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #470414
Posted Monday, March 17, 2008 12:32 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Change the final While loop to get rid of any spaces (right now, it gets rid of double-spaces), and it should do what you need.

If you have extended characters, you'll need to add the ranges for those to the thing.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #470415
Posted Monday, March 17, 2008 12:42 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I haven't tested this enough to be sure, but it should work:

create function dbo.NumberClean
(@String_in nvarchar(100))
returns bigint
as
begin
declare @NumberStr nvarchar(100), @Number_out bigint

;with Chars (Seq, Chr) as
(select number, substring(@string_in, number, 1)
from dbo.Numbers
where number between 0 and len(@string_in))

select @numberstr = coalesce(@numberstr + chr, chr)
from chars
where chr like '[0-9]'

select @number_out = @numberstr

return @number_out
end



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #470422
Posted Monday, March 17, 2008 1:03 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:06 AM
Points: 2,278, Visits: 3,052
You can also use regular expression functions.

regular expressions function
CREATE FUNCTION dbo.regexReplace
(
@source varchar(5000),
@regexp varchar(1000),
@replace varchar(1000),
@globalReplace bit = 0,
@ignoreCase bit = 0
)
RETURNS varchar(1000) AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @result varchar(5000)

EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 BEGIN
EXEC @hr = sp_OADestroy @objRegExp
RETURN NULL
END
EXECUTE @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
RETURN NULL
END

RETURN @result
END
GO

the code to get the result:

select dbo.regexReplace(MyColumn, '[^0-9]', '', 1, 1 ) 
from MyTable

Link with a list of regular expression functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205




My blog: http://jahaines.blogspot.com
Post #470437
Posted Monday, March 17, 2008 1:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 12, 2011 12:48 PM
Points: 86, Visits: 58
How about this?

declare @pos smallint
declare @string varchar(100)

set @string = '1109A><":{$%^&*4DSE2@!~$%^&567KJHGT'

while isnumeric(@string+'e0') = 0
begin

set @pos = (select patindex('%[^0-9]%',@string))
set @string = (select replace(@string,substring(@string,@pos,1),''))

end

select @string


there is a reson to add 'e0' to the string. check this out,

select IsNumeric('1002e0')


in this case IsNumeric returns 1. So in the above example @String never get replaced with ''. Adding 'e0' again would resolve that.

read more about this:

http://www.tek-tips.com/faqs.cfm?fid=6423
Post #470441
Posted Monday, March 17, 2008 6:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 36,760, Visits: 31,215
GSquared (3/17/2008)
I haven't tested this enough to be sure, but it should work:


Absolutely the right idea with the numbers table, Gus :) but you don't need the CTE or a derived table.

 CREATE FUNCTION dbo.fnDigitsOnly (@pString VARCHAR(8000))
-- Tally table can be found at http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @CleanString VARCHAR(8000)
SELECT @CleanString = ISNULL(@CleanString,'')+SUBSTRING(@pString,N,1)
FROM dbo.Tally WITH (NOLOCK)
WHERE N<=LEN(@pString)
AND SUBSTRING(@pString,N,1) LIKE ('[0-9]')
RETURN @CleanString
END



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #470579
Posted Tuesday, March 18, 2008 2:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:18 AM
Points: 150, Visits: 803
Thanks to all concerned.

Sorry I'm late getting back, but I'll try them out now.

Very much appreciated!
Post #470677
Posted Tuesday, March 18, 2008 8:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Thanks Jeff. That's definitely better. (As mentioned, mine was thrown together and tested against one case.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #470829
Posted Tuesday, March 18, 2008 8:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 36,760, Visits: 31,215
Sorry Gus... I actually didn't mean it that way (better)... I just meant it's different and you don't need the CTE if you don't want it. They both run pretty darned fast compared to the While loop methods.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #470836
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse