sql-server - sql字符串中的随机字符

Question

我需要用随机字母或数字替换所有 ? 符号。

所以我尝试了这个:

REPLACE (RGFLT, '?', LEFT(NEWID(), 1))

这段代码不够好，因为它每行生成一次随机符号。我需要的是字符串中每个 ? 的随机符号。

数据样本:

AA ?????
BCZ ???Z?
B?? ???

Answer 1

设置数据

DECLARE @t table (
   a varchar(50)
);

INSERT INTO @t (a)
  VALUES ('A?A??')
       , ('BBBBB')
       , ('C??CC')
       , ('??DD?')
       , ('?????')
       , ('?')
       , ('ABCDEF??ASG?AG?GE?E?£%$ H?EHH?SN?S SA? ? !??" ')
;

---

方法一——递归CTE

; WITH x AS (
  SELECT a As original_value
       , numbers.number As sequence
       , SubString(a, numbers.number, 1) As to_replace
       , Left(NewID(), 1) As replace_char
  FROM   @t As t
   LEFT
    JOIN dbo.numbers
      ON numbers.number BETWEEN 1 AND Len(t.a)
)
, y AS (
  SELECT original_value
       , sequence
       , CASE WHEN to_replace = '?' THEN replace_char ELSE to_replace END As to_use
  FROM   x As x1
)
, z AS (
  SELECT original_value
       , sequence
       , to_use
       , Cast(to_use As varchar(max)) As new_value
  FROM   y
  WHERE  sequence = 1 -- anchor

   UNION ALL

    SELECT z.original_value
         , y.sequence
         , y.to_use
         , z.new_value + y.to_use
    FROM   z
     INNER
      JOIN y
        ON y.original_value = z.original_value
       AND y.sequence - 1 = z.sequence
)
SELECT original_value
     , new_value
FROM   z
WHERE  sequence = Len(original_value)
;

[示例] 结果:

original_value                                     new_value
-------------------------------------------------- ---------------------------------------------
?                                                  F
ABCDEF??ASG?AG?GE?E?£%$ H?EHH?SN?S SA? ? !??"      ABCDEF4DASG7AGFGE5E2£%$ H7EHHASN2S SAF 1 !77"
?????                                              CB347
??DD?                                              43DD2
C??CC                                              C31CC
BBBBB                                              BBBBB
A?A??                                              A7A99

这是它的工作原理:

1. 使用数字表将原始值拆分为每个字符一行。
2. 找出我们要替换的字符串 (?) 并为这些字符串生成一个随机字符。对于那些我们不想替换的，保留原来的字符。
3. 使用递归基本上一次将 1 个字符重新构建字符串
4. 过滤以显示“最终”值

---

方法 2 - 内容(FORXML)

; WITH x AS (
  SELECT a As original_value
       , numbers.number As sequence
       , SubString(a, numbers.number, 1) As to_replace
       , Left(NewID(), 1) As replace_char
  FROM   @t As t
   LEFT
    JOIN dbo.numbers
      ON numbers.number BETWEEN 1 AND Len(t.a)
)
, y AS (
  SELECT original_value
       , sequence
       , CASE WHEN to_replace = '?' THEN replace_char ELSE to_replace END As to_use
  FROM   x As x1
)
SELECT DISTINCT
       original_value
     , Replace(Stuff((
         SELECT '|' + z.to_use
         FROM   y As z
         WHERE  z.original_value = y.original_value
         ORDER
             BY z.sequence
         FOR XML PATH(''))
       , 1, 1, ''), '|', '') As new_value
FROM   y
;

[示例] 结果:

original_value                                     new_value
-------------------------------------------------- ---------------------------------------------
?                                                  5
?????                                              10A30
??DD?                                              7EDDC
A?A??                                              AEA23
ABCDEF??ASG?AG?GE?E?£%$ H?EHH?SN?S SA? ? !??"      ABCDEFE8ASGEAG0GEBE4£%$ H8EHH2SNCS SAE 1 !1E"
BBBBB                                              BBBBB
C??CC                                              C34CC

这是它的工作原理:

步骤 1 和 2 与方法 1 相同...

1. 使用用于字符串连接的 [yucky!] Stuff(FOR XML) hack 将值强制组合回单个字符串值。

不漂亮，但它有效!