tsql - 如何在 SQL 中生成随机格式的字符串

Question

基于 Martin Smith's回答here ，我一直在尝试使用一种非常相似的技术来创建 10000 个随机字符串，这些字符串以两个字符 (A-Z) 开头，然后是两个数字 (0-9)，然后是两个字符 (A-Z)。任何人都知道如何使用相同的技术来做到这一点，或者这是不可能的，提前致谢保罗

Answer 1

对 original 稍作修改:

DECLARE @Numbers  TABLE
(
n INT PRIMARY KEY
);


WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),   --2
        E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
        E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
        E08(N) AS (SELECT 1 FROM E04 a, E04 b), --256
        E16(N) AS (SELECT 1 FROM E08 a, E08 b)  --65,536
INSERT INTO @Numbers
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM E16

/* 
 1.  Build a table variable of numbers
 2.  For each row in the table (up to row 1000)
 3.  Get the top 2 numbers from the table variable
 4.  For each number, get the absolute value of the checksum of a GUID
 5.  This value will always be a positive integer
 6.  Get the remainder of that integer when divided by 26
 7.  This will always be a number between 0 and 25
 8.  If the absolute value number is divisible by 2, add 65 to the number (uppercase)
 9.  Otherwise, add 97 to the number (lowercase)
 10. Use that number as an ASCII value and get the character representation
 11. Use FOR XML PATH to convert the two characters in the subquery to a string
 12. Repeat the same logic for 2 numbers (use %9 to get two numbers between 0 and 9) 
 13. Repeat the same logic from previous steps to get two more alphabetic characters
*/

SELECT CAST((SELECT TOP 2 CHAR(CASE
                                  WHEN Abs(Checksum(Newid()))%2 = 0 THEN 65
                                  ELSE 97
                                END + Abs(Checksum(Newid()))%26)
             FROM   @Numbers n1
             WHERE  n1.n >= -n2.n 
             FOR XML PATH('')) AS CHAR(2)) + 
       CAST((SELECT TOP 2 Abs(Checksum(Newid()))%9
             FROM   @Numbers n1
             WHERE  n1.n >= -n2.n 
             FOR XML PATH('')) AS CHAR(2)) +             
       CAST((SELECT TOP 2 CHAR(CASE
                                  WHEN Abs(Checksum(Newid()))%2 = 0 THEN 65
                                  ELSE 97
                                END + Abs(Checksum(Newid()))%26)
             FROM   @Numbers n1
             WHERE  n1.n >= -n2.n /*So it gets re-evaluated for each row!*/
             FOR XML PATH('')) AS CHAR(2))
FROM   @Numbers n2