Validate and Search Phone Numbers Using Functional Index(Example)

--Create the table

create table temp (id int, phone nvarchar(100))

insert into temp select 1, '123-456-7890 x 132'

insert into temp select 2, 'my phone is 823-456-7890 ext 134'GO

--Create a function removing all non-digit characters and then the leading "1" from a free format phone number
create FUNCTION [dbo].[udfGetPhoneDigits](@phone
)NVARCHAR(100)
RETURNS
NVARCHAR(100)WITH
AS SCHEMABINDING
BEGIN
-- Remove non-digit characters
WHILE PATINDEX('%[^0-9]%', @phone) > 0
SET @phone = REPLACE(@phone, SUBSTRING(@phone,PATINDEX('%[^0-9]%', @phone),1),'') Return right(@phone, len(@phone) - case left(@Phone,1) when '1' then 1 else 0 end)
END
GO
-- Add a computed column
Alter table temp add PhoneDigits AS dbo.udfGetPhoneDigits(phone) PERSISTED
GO
-- Create the index
Create index idx1 on temp ( PhoneDigits)
GO
-- Ready for indexed search
select * from temp where phonedigits = dbo.udfGetPhoneDigits('my phone is 823-456-7890 ext 134')