Best SQL Replacement

Question

I wanted to know if there is a better way to write this update.

update Alumni_Export_New
set phone = replace(replace(replace(replace(phone,'-',''),' ',''),')',''),'(','')
from tbl

I have a stored procedure in MSSQL 2005 to clear the phone field in a table and thought there should be a better way to do this, and then what I have.

Answer 1

Use CLR integration and regular expressions .

Answer 2

, REPLACE, . , , , .

:

UPDATE Alumni_Export_New
SET phone = dbo.StripPhoneNumber(phone)
FROM tbl

Answer 3

Microsoft SQl Server, , (UDF),

Answer 4

, MySQL, , REPLACE() (, TRANSLATE() dbs).

Answer 5

Oracle translate.

update Alumni_Export_New set phone = translate(phone, '- ()', '') from tbl 

Answer 6

make loop , i

    CREATE FUNCTION fn_retun_only_numbers
(
    @myOrgString varchar(50)
)
RETURNS  varchar(50)
AS
BEGIN
    declare @lenOfString int, @i int
    declare @oneChar varchar(1),@newString varchar(50)


    set @lenOfString = (select len(@myOrgString))
    set @i = 1
    set @newString= ''

    while  (@lenOfstring>=@i)
    begin
    set @oneChar = substring(@myOrgString,@i,1)

if ((@oneChar)in ('0','1','2','3','4','5','6','7','8','9'))
begin
set @newString=@newString+@oneChar
end
set @i=@i+1
end
return @newString
END
GO

, ,

update Alumni_Export_New
set phone = dbo.fn_retun_only_numbers(phone)
from tbl

I don't have SQL at home, so maybe there are errors in the code

This is not the best solution, but if you only need to always receive numbers, this is one of the possible approaches.