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')

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

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')

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