Random char in tsql string

Question

I need to replace all ? symbols with random letters or numbers.

So I tried this:

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

This code is not good enough, because it generate random symbol once per each row. What I need is random symbol for every ? in string.

Data sample:

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

Answer 1

Setup data

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? ? !??" ')
;

---

Method 1 - Recursive 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)
;

[Example] Results:

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

Here's the jist of how it works:

1. Use a numbers table to split the original value in to a single row per character.
2. Work out which strings we want to replace (?) and generate a random character for these. For those we don't want to replace, keep the original character.
3. Use recursion to basically build the string back together 1 character at a time
4. Filter to show the "final" value

---

Method 2 - Stuff(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
;

[Example] Results:

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

Here's the jist of how it works:

Steps 1 & 2 same as method 1...

3. Use the [yucky!] Stuff(FOR XML) hack used for string concatenation to brute force the values back together in to a single string value.

Not pretty, but it works!

Answer 2

Note that I have made the following assumptions

• the max count of sequence ? characters is 5
• if you get two equal counts of ? sequences there are going to be replaced with same numbers. For example, the text TEST ?? TEST ?? is going to be transformed like this TEST 12 TEST 12

AS the solution is not dynamic (you need to change it to work in your case).

---

DECLARE @DataSource TABLE
(
    [Text] VARCHAR(128)
);

INSERT INTO @DataSource ([Text])
VALUES ('AA ?????')
      ,('BCZ ???Z?')
      ,('B?? ???')
      ,('C?? ???')
      ,('D?? ???');


SELECT [Text]
      ,REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE([Text], '?????', LEFT(ABS(CHECKSUM(NEWID())) % 1000000 + 100000, 5))
                    , '????',LEFT(ABS(CHECKSUM(NEWID())) % 1000000 + 100000, 4))
                , '???',LEFT(ABS(CHECKSUM(NEWID())) % 1000000 + 100000, 3))
            , '??',LEFT(ABS(CHECKSUM(NEWID())) % 1000000 + 100000, 2))
        , '?',LEFT(ABS(CHECKSUM(NEWID())) % 1000000 + 100000, 1))
FROM @DataSource;