Is there an easier way to handle this Replace method? 1

[Kenos]

I have phone numbers in a nvarchar(20) Column I need to remove all
Non numeric Characters and remove extra number leaving a 10 digit number

Is there to replace using like % a-z %

This is what I am using now

CONVERT( varchar(10),REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(ShippingPhone))},CHAR(13),''),CHAR(9),''),CHAR(10),''),',',''),'-',''),'.',''),')',''),'(',''),' ',''),'/',''))

 

[zhavic]

Try to create function:

CREATE FUNCTION fn_phone_number
(
@cInput_phone_number AS char(20)
)
RETURNS char(10)
AS
BEGIN
DECLARE @i integer
DECLARE @cOne_char char(1)

SET @i = 1

/* loop through phone number */
WHILE @i < LEN( @cInput_phone_number )
BEGIN
IF SUBSTRING( @cInput_phone_number, @i, 1 ) NOT BETWEEN '0' AND '9'
/* delete non numeric character */
SELECT @cInput_phone_number = STUFF( @cInput_phone_number , @i , 1 , '' )
ELSE
SET @i = @i + 1
END

RETURN CAST( @cInput_phone_number AS char(10) )
END


************************
and then

SELECT dbo.fn_phone_number( '123.%45/578' )

Zhavic

 

[mitrab22]

Why can't u use the substring function?

 

[Kenos]

Thanks Zhavic

This Function works Great

Thanks for the Help