Formatare numere telefon

Configurare noua (How To)

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’)

Tip solutie

Permanent
Etichetare:

Voteaza

(7 din 12 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?