|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Friday, January 27, 2012 9:03 AM
Points: 122,
Visits: 638
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 13,508,
Visits: 8,070
|
|
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.)
- 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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 13,508,
Visits: 8,070
|
|
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.
- 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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 13,508,
Visits: 8,070
|
|
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
- 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
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Wednesday, December 07, 2011 10:54 AM
Points: 2,257,
Visits: 2,849
|
|
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
|
|
|
|
|
SSC 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
|
|
|
|
|
One Orange Chip
Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 28,313,
Visits: 22,110
|
|
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, January 27, 2012 9:03 AM
Points: 122,
Visits: 638
|
|
Thanks to all concerned.
Sorry I'm late getting back, but I'll try them out now.
Very much appreciated!
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 13,508,
Visits: 8,070
|
|
Thanks Jeff. That's definitely better. (As mentioned, mine was thrown together and tested against one case.)
- 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
|
|
|
|
|
One Orange Chip
Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 28,313,
Visits: 22,110
|
|
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
