Nested replace() functions: a better way?

[homer13j]

Cleaning up phone number data imported from Excel. This admittedly ugly script works, but I can't help but wonder if there's a better or more efficient way of doing this than multiple nested calls to the replace() function?

-- clean up data
UPDATE prmd_company set 
phone = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(ltrim(phone)), 'Ph: ', ''), '(', ''), ')', ''), '-', ''), 'ext', 'x'), '+', ''), '.', ''), ':', ''), ' ', ''), CHAR(160), ''),
fax = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(ltrim(fax)), 'Fax: ', ''), '(', ''), ')', ''), '-', ''), 'ext', 'x'), '+', ''), '.', ''), ':', ''), ' ', ''), CHAR(160), ''),

[TysonLPrice]

I'm assuming you mean SQL Server and just glancing at it you only want number? Here is a link to something you might be able to use:

http://blogs.lessthandot.com/index.p...th-sql-server/

I changed the function to:

Create Function dbo.GetNumbers(@Data VarChar(8000))
Returns VarChar(8000)
AS
Begin	
    Return Left(
             SubString(@Data, PatIndex('%[0-9-]%', @Data), 8000), 
             PatIndex('%[^0-9-]%', SubString(@Data, PatIndex('%[0-9-]%', @Data), 8000) + 'X')-1)
End

to not allows periods.

Same principle here...I like this guys site:

http://blog.sqlauthority.com/2008/10...-numbers-only/

[digitalShaman]

this multiple replaces are truely ugly. i do not think that there is a built in way around it but you could write your own multi replace:

-- =============================================
-- Author:		the digital shaman
-- Create date: Jul 2014
-- usage: select dbo.multireplace('ph ext 1ph3- 78','ph,|ext,x|-,|')
-- =============================================
CREATE FUNCTION [dbo].[MultiReplace] (@Input varchar(500),@Replacements varchar(500)) RETURNS varchar(500) AS
BEGIN
	while @Replacements <> ''
		begin
			declare @Item varchar(20)=SUBSTRING(@Replacements,1,PATINDEX('%|%',@Replacements)-1)
			declare @Search varchar(20)='%' + SUBSTRING(@item,1,PATINDEX('%,%',@item)-1) + '%'
			declare @Replace varchar(20)=SUBSTRING(@item,PATINDEX('%,%',@item)+1,20)
			while PATINDEX(@Search,@Input)>0
				set @Input = STUFF(@Input,PATINDEX(@Search,@Input),LEN(@Search)-2,@Replace)
			set @Replacements=SUBSTRING(@Replacements, len(@Item)+2,500)
		end

    
    Return @Input
END

this is quickly fiddled together so please dont blame me if it fails in some cases...