Data Cleansing

There’s also a standard scalar function FS_REMOVE_NON_ALPHANUMERICS or FS_REMOVE_NON_NUMERICS, which will do this for you.

Sample:

select top 100

       phone,

       phone_clean = dbo.FS_REMOVE_NON_NUMERICS(phone)

from   dbo.T_PHONE

=>Ryan Creps

@Tessitura Network

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Travis Armbuster
Sent: Tuesday, July 1, 2014 11:00 AM
To: Ryan Creps
Subject: Re: [Tessitura Technical Forum] Data Cleansing

Give this a shot and see if this will help you out with stripping the non- numeric numbers.

/*******************************

example

**********************************/

declare @phone table(
phone Varchar(25)
)

insert into @phone values('1///(202)##600-4165Home',null)

declare @strphone varchar(max)
set @strphone = (select phone from @phone)

BEGIN
    WHILE PATINDEX('%[^0-9]%', @strphone) > 0
    BEGIN
        SET @strphone =  stuff(@strphone,PATINDEX('%[^0-9]%',@strphone),1,'')
    END
   select (select phone from @phone) as Orginal_Phone, @strphone as clean_phone
end

If this is the result you are looking for, you could create a function the strip all the non-numeric numbers.

example:

/**********************************************************************************
Function CleanUpPhones
**********************************************************************************/

CREATE Function [LFT_CleanUpPhones](@strphone VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strphone) > 0
    BEGIN
        SET @strphone =  stuff(@strphone,PATINDEX('%[^0-9]%',@strphone),1,'')
    END
  RETURN @strphone
end

GO

Grant select on [dbo].[LFT_CleanUpPhones] to impusers

go

Hope it helps.

Travis