sql-server - 在字符串中查找多个空格的表达式

Question

我们处理大量敏感数据，我想仅使用每个姓名部分的第一个和最后一个字母来掩盖乘客姓名，并用三个星号 (***) 将它们连接起来，

For example: the name 'John Doe' will become 'J***n D***e'

对于由两部分组成的名称，可以通过使用表达式查找空格来实现:

LEFT(CardHolderNameFromPurchase, 1) + 
 '***' + 
 CASE WHEN CHARINDEX(' ', PassengerName) = 0 
      THEN RIGHT(PassengerName, 1) 
      ELSE SUBSTRING(PassengerName, CHARINDEX(' ', PassengerName) -1, 1) +
           ' ' + 
           SUBSTRING(PassengerName, CHARINDEX(' ', PassengerName) +1, 1) +
           '***' + 
           RIGHT(PassengerName, 1) 
 END

不过，乘客姓名可以有两部分以上，没有实际限制。我应该如何找到表达式中所有空格的索引？还是我应该以不同的方式解决这个问题？

非常感谢任何帮助或指示!

Answer 1

根据 Gordon 在他的回答中的解释，此解决方案可以满足您的要求，但在尝试隐藏个人身份数据时确实是错误的方法。

SQL:

declare @t table(n nvarchar(20));
insert into @t values('John Doe')
,('JohnDoe')
,('John Doe Two')
,('John Doe Two Three')
,('John O''Neill');

select n
      ,stuff((select ' ' + left(s.item,1) + '***' + right(s.item,1)
              from dbo.fn_StringSplit4k(t.n,' ',null) as s
              for xml path('')
              ),1,1,''
              ) as mask
from @t as t;

输出:

+--------------------+-------------------------+
|         n          |          mask           |
+--------------------+-------------------------+
| John Doe           | J***n D***e             |
| JohnDoe            | J***e                   |
| John Doe Two       | J***n D***e T***o       |
| John Doe Two Three | J***n D***e T***o T***e |
| John O'Neill       | J***n O***l             |
+--------------------+-------------------------+

---

基于Jeff Moden's Tally Table approach的字符串拆分函数:

create function [dbo].[fn_StringSplit4k]
(
  @str nvarchar(4000) = ' '    -- String to split.
 ,@delimiter as nvarchar(1) = ','  -- Delimiting value to split on.
 ,@num as int = null      -- Which value to return, null returns all.
)
returns table
as
return
     -- Start tally table with 10 rows.
 with n(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)

     -- Select the same number of rows as characters in @str as incremental row numbers.
     -- Cross joins increase exponentially to a max possible 10,000 rows to cover largest @str length.
  ,t(t)   as (select top (select len(isnull(@str,'')) a) row_number() over (order by (select null)) from n n1,n n2,n n3,n n4)

     -- Return the position of every value that follows the specified delimiter.
  ,s(s)   as (select 1 union all select t+1 from t where substring(isnull(@str,''),t,1) = @delimiter)

     -- Return the start and length of every value, to use in the SUBSTRING function.
     -- ISNULL/NULLIF combo handles the last value where there is no delimiter at the end of the string.
  ,l(s,l) as (select s,isnull(nullif(charindex(@delimiter,isnull(@str,''),s),0)-s,4000) from s)

 select rn
          ,item
 from(select row_number() over(order by s) as rn
    ,substring(@str,s,l) as item
  from l
  ) a
 where rn = @num
  or @num is null;
GO