IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'f_4P_phone')
DROP FUNCTION f_4P_phone
GO
CREATE FUNCTION f_4P_phone
(@phone varchar(21))
Returns varchar(30)
AS
begin
declare @out varchar(200)
set @out = ''
declare @phone2 varchar(20)
set @phone2 = ''
declare @pos as int
set @pos = 0
--remove any special chars
while @pos < len(@phone) begin
set @pos = @pos + 1
if isnumeric(substring(@phone,@pos,1)) > 0 and substring(@phone,@pos,1) <> '-' begin
set @phone2 = @phone2 + substring(@phone,@pos,1)
end
end
if @phone is null or @phone = '' begin
return('')
end
if len(@phone2) >= 10 begin
set @out = '('
set @out = @out + substring(@phone2,1,3)
set @out = @out + ') '
set @out = @out + substring(@phone2,4,3)
set @out = @out + '-'
set @out = @out + substring(@phone2,7,4)
if substring(@phone2,11,99) > '' begin
set @out = @out + ' ' + substring(@phone2,11,99)
end
return (@out)
end
if len(@phone2) = 7 begin
set @out = @out + substring(@phone2,1,3)
set @out = @out + '-'
set @out = @out + substring(@phone2,4,4)
return (@out)
end
set @out = @phone2
return (@out)
end
go
grant all on f_4P_phone to public