[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Cleaning out non numbers from a field

Jeffrey42

3/28/2007 8:18:00 PM

This is kind of a follow up to a question I asked a long time ago.

I am in the process of cleaning out the phone number field of a
table. The field is a varchar and all kinds of different things have
gotten into it. I was thinking that this should not have been a very
unique situation but have not found anything that would clean out the
non numbers in a field, does anyone know of anything that would do
this other than a large number of replace statements.
3 Answers

Anith Sen

3/28/2007 8:26:00 PM

0

See: www.projectdmx.com/tsql/strcleanup.aspx

--
Anith


TheSQLGuru

3/28/2007 9:17:00 PM

0

It doesn't seem that any of the listed methods are bullet proof for removing
all non-numeric data from a varchar telephone number.

Possible value for current phone number ' 2#^7)213 454-33'. Note that
non-printable characters can get into the database as well.

Believe it or not, probably the most efficient ROBUST method is to cursor
through each value then iterating through each character removing (or
accepting it) based on whether or not it is 0-9. This assumes you are
looking for a 7 or 10 digit number formatted thusly: 2721345433. You could
chose to put these corrected values into a table variable/temp table with
the associated row's key then do a batch update to apply them to the
permanent table.


--
TheSQLGuru
President
Indicium Resources, Inc.

"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:ew4KFdXcHHA.3960@TK2MSFTNGP04.phx.gbl...
> See: www.projectdmx.com/tsql/strcleanup.aspx
>
> --
> Anith
>


Anith Sen

3/28/2007 10:45:00 PM

0

>> It doesn't seem that any of the listed methods are bullet proof for
>> removing all non-numeric data from a varchar telephone number.

Well, it is scrubbing & SQL is not the best choice to begin with. So we are
left with whatever is available at our disposal. The variation of the
approach you described is already among the listed methods.

CREATE FUNCTION dbo.udf ( @s VARCHAR(20) )
RETURNS VARCHAR(20) AS BEGIN
WHILE PATINDEX( '%[^0-9]%', @s ) > 0
SET @s = REPLACE( @s, SUBSTRING( @s,
PATINDEX( '%[^0-9]%', @s ), 1 ), '' )
RETURN @s END
GO

SELECT dbo.udf(' 2#^7)213 454-33' ) ;

--
Anith