Cacher is the code snippet organizer for pro developers

--CREATE FUNCTION [dbo].[fn_StripCharacters]
--(
--    @String NVARCHAR(MAX), 
--    @MatchExpression VARCHAR(255)
--)
--RETURNS NVARCHAR(MAX)
--AS
--BEGIN
--    SET @MatchExpression =  '%['+@MatchExpression+']%'

--    WHILE PatIndex(@MatchExpression, @String) > 0
--        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

--    RETURN @String

--END

select 
avt_mobilephone, 
REVERSE(dbo.fn_StripCharacters(avt_mobilephone, '^0-9')) as avt_mobilephone_reversed,
telephone1,
REVERSE(dbo.fn_StripCharacters(telephone1, '^0-9')) as avt_telephone1_reversed,
telephone2,
REVERSE(dbo.fn_StripCharacters(telephone2, '^0-9')) as avt_telephone2_reversed
from
Account
WHERE 
avt_mobilephone IS NOT NULL OR telephone1 IS NOT NULL OR telephone2 IS NOT NULL

UPDATE Account
SET 
avt_mobilephone_reversed = REVERSE(dbo.fn_StripCharacters(avt_mobilephone, '^0-9')),
avt_telephone1_reversed = REVERSE(dbo.fn_StripCharacters(telephone1, '^0-9')),
avt_telephone2_reversed = REVERSE(dbo.fn_StripCharacters(telephone2, '^0-9'))
FROM Account
WHERE avt_mobilephone IS NOT NULL OR telephone1 IS NOT NULL OR telephone2 IS NOT NULL

UPDATE Lead
SET 
avt_mobilephone_reversed = REVERSE(dbo.fn_StripCharacters(mobilephone, '^0-9')),
avt_telephone1_reversed = REVERSE(dbo.fn_StripCharacters(telephone1, '^0-9')),
avt_telephone2_reversed = REVERSE(dbo.fn_StripCharacters(telephone2, '^0-9'))
FROM Lead
WHERE mobilephone IS NOT NULL OR telephone1 IS NOT NULL OR telephone2 IS NOT NULL

select 
avt_mobilephone, 
avt_mobilephone_reversed,
telephone1,
avt_telephone1_reversed,
telephone2,
avt_telephone2_reversed
from
Account
WHERE 
avt_mobilephone IS NOT NULL OR telephone1 IS NOT NULL OR telephone2 IS NOT NULL

select 
mobilephone, 
avt_mobilephone_reversed,
telephone1,
avt_telephone1_reversed,
telephone2,
avt_telephone2_reversed
from
Lead
WHERE 
mobilephone IS NOT NULL OR telephone1 IS NOT NULL OR telephone2 IS NOT NULL