在 SQL Server 查询/视图中应用掩码来格式化字符串

Apply a Mask to Format a String in SQL Server Query/View

提问by Molloch

Is there a neat way to apply a mask to a string in a SQL Server query?

有没有一种巧妙的方法可以将掩码应用于 SQL Server 查询中的字符串？

I have two tables, one with Phone number stored as varchar with no literals 0155567890and a phone type, which has a mask for that phone number type: (##) #### ####

我有两个表，一个电话号码存储为 varchar 没有文字0155567890和电话类型，它具有该电话号码类型的掩码：(##) #### ####

What is the best way to return a string (for a merge Document) so that the query returns the fully formatted phone number:

返回字符串（对于合并文档）以便查询返回完全格式化的电话号码的最佳方法是什么：

[phone]

采纳答案by Jeff S.

I needed this also, and thanks to Sjuul's pseudocode, I was able to create a function to do this.

我也需要这个，多亏了 Sjuul 的伪代码，我才能够创建一个函数来做到这一点。

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

回答by i-one

Just in case if someone will ever need table valued function.

以防万一有人需要表值函数。

Approach 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

Then apply it as

然后将其应用为

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

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

---

---

Approach 2

方法二

Let's leave abowe one for history. Below one is more 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

回答by Sjuul Janssen

This is just what came up in my head. I don't know whether it's the best solution but I think it should be workable.

这正是我脑海中浮现的。我不知道这是否是最好的解决方案，但我认为它应该是可行的。

Make a function with the name applyMask (orso)

创建一个名为 applyMask (orso) 的函数

Pseudocode:

伪代码：

WHILE currentPosition < Length(PhoneNr) AND safetyCounter < Length(Mask)
    IF currentSign = "#"
        result += Mid(PhoneNr, currentPosition, 1)
        currentPosition++
    ELSE
        result += currentSign
        safetyCounter++
    END
END
Return result

回答by Goran B.

If you need to "mask", rather hide the real value with another, and then "unmask" a string you can try this function, or extend it for that matter. :)

如果您需要“屏蔽”，而不是用另一个隐藏真实值，然后“取消屏蔽”一个字符串，您可以尝试这个函数，或者为此扩展它。:)

https://stackoverflow.com/a/22023329/2175524

https://stackoverflow.com/a/22023329/2175524