Appliquer un Masque de formatage de Chaîne de Requête de SQL Server/Afficher

J'avais besoin de cela, et grâce à Sjuul du pseudo-code, j'ai été en mesure de créer une fonction pour faire cela.

CREATE FUNCTION [dbo].[fx_FormatUsingMask] 
(
    -- Add the parameters for the function here
    @input nvarchar(1000),
    @mask nvarchar(1000)
)
RETURNS nvarchar(1000)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result nvarchar(1000) = ''
    DECLARE @inputPos int = 1
    DECLARE @maskPos int = 1
    DECLARE @maskSign char(1) = ''

    WHILE @maskPos <= Len(@mask)
    BEGIN
        set @maskSign = substring(@mask, @maskPos, 1)

        IF @maskSign = '#'
        BEGIN
            set @result = @result + substring(@input, @inputPos, 1)
            set @inputPos += 1
            set @maskPos += 1
        END
        ELSE
        BEGIN
            set @result = @result + @maskSign
            set @maskPos += 1
        END
    END
    -- Return the result of the function
    RETURN @result

END

OriginalL'auteur Jeff S.

Juste au cas où si quelqu'un aurez jamais besoin de la fonction table.

Approche 1

create function ftMaskPhone
(
    @phone varchar(30),
    @mask varchar(50)
)
returns table as
return
    with ci(n, c, nn) as (
        select
            1,
            case
                when substring(@mask, 1, 1) = '#' then substring(@phone, 1, 1)
                else substring(@mask, 1, 1)
            end,
            case when substring(@mask, 1, 1) = '#' then 1 else 0 end
        union all
        select
            n + 1,
            case
                when substring(@mask, n + 1, 1) = '#' then substring(@phone, nn + 1, 1)
                else substring(@mask, n + 1, 1)
            end,
            case when substring(@mask, n + 1, 1) = '#' then nn + 1 else nn end
        from ci where n < len(@mask))
    select (select c + '' from ci for xml path(''), type).value('text()[1]', 'varchar(50)') PhoneMasked
GO

Puis de l'appliquer comme

declare @mask varchar(50)
set @mask = '(##) #### ####'

select pm.PhoneMasked
from Phones p
    outer apply ftMaskPhone(p.PhoneNum, @mask) pm

---

Approche 2

Laissons abowe pour l'histoire. Ci-dessous l'un est plus performant.

create function ftMaskPhone
(
@phone varchar(30),
@mask varchar(50)
)
returns table as
return
with v1(N) as (
select 1 union all select 1 union all select 1 union all select 1 union all select 1
union all
select 1 union all select 1 union all select 1 union all select 1 union all select 1
),
v2(N) as (select 1 from v1 a, v1 b),
v3(N) as (select top (isnull(len(@mask), 0)) row_number() over (order by @@spid) from v2),
v4(N, C) as (
select N, isnull(substring(@phone, case when c.m = 1 then row_number() over (partition by c.m order by N) end, 1), substring(@mask, v3.N, 1))
from v3
cross apply (select case when substring(@mask, v3.N, 1) = '#' then 1 end m) c
)
select Value = (
select c + ''
from v4
order by N
for xml path(''), type
).value('text()[1]', 'varchar(50)')
go

OriginalL'auteur i-one