ReversePhoneNumbers
--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