Situatie
Mai jos este o functie ce primeste ca parametru o coloana de tip varchar care este folosita pentru stocarea numereleor de telefon si le formateaza uniform si lizibil.
Solutie
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[FormatPhone_fct](
@phone NVARCHAR(60)
)
RETURNS NVARCHAR(60)
AS
BEGIN
DECLARE @lenPhone INT ,@phoneStr NVARCHAR(30)
WHILE PATINDEX(‘%[^0-9]%’, @phone) > 0
SET @phone = REPLACE(@phone,
SUBSTRING(@phone,PATINDEX(‘%[^0-9]%’, @phone),1),”)
SET @lenPhone = LEN(@phone)-10
IF SUBSTRING(@phone,1,1) =’1′ and LEN(@phone) >10
BEGIN
SET @phone=SUBSTRING(@phone,2,LEN(@phone)-1)
END
SET @phoneStr=@phone
SET @phone = SUBSTRING(@phone,1,4)+
‘-‘ + SUBSTRING(@phone,5,2) +’ ‘
+ SUBSTRING(@phone,7,2) +’ ‘
+ SUBSTRING(@phone,9,2)
IF @lenPhone > 1
SET @phone=@phone +’ ext: ‘+ SUBSTRING(@phoneStr,11,@lenPhone)
RETURN @phone
END
— select [dbo].[FormatPhone_fct] (‘0721123456’)
Leave A Comment?