SQL SERVER使用Regex模式生成数据

好吧，我可以为您提供一个不基于正则表达式而是基于一组参数的解决方案-但是它包含您所有要求的完整集合。
我已经将此解决方案基于我编写的用于生成随机字符串的用户定义函数（您可以在此处阅读我的博客文章）-我刚刚对其进行了更改，以便它可以根据以下条件：

• 掩码具有可选的前缀。
• 面罩有一个可选的后缀。
• 掩码具有可变长度的随机字符串。
• 随机字符串可以包含小写字母，大写字母，数字或以上任意组合。

我已根据您对问题的更新决定了这些规则集，其中包含所需的掩码：

(d){7} 7895623(W){5} ABCDEFTest_Product_(d){1,4}        Test_Product_0070.(d){2}         0.59https://www.(l){10}.com    https://www.anything.com

现在，对于代码：
由于我使用的是用户定义的函数，因此无法在其中使用

NewId()

CREATE VIEW GuidGeneratorAS    SELECt Newid() As NewGuid;

在函数中，我们将使用该视图来生成a

NewID()

该函数本身比起我开始使用的随机字符串生成器要麻烦得多：

CREATE FUNCTION dbo.MaskGenerator(    -- use null or an empty string for no prefix    @Prefix nvarchar(4000),     -- use null or an empty string for no suffix    @suffix nvarchar(4000),     -- the minimum length of the random part    @MinLength int,     -- the maximum length of the random part    @MaxLength int,     -- the maximum number of rows to return. Note: up to 1,000,000 rows    @Count int,     -- 1, 2 and 4 stands for lower-case, upper-case and digits.     -- a bitwise combination of these values can be used to generate all possible combinations:    -- 3: lower and upper, 5: lower and digis, 6: upper and digits, 7: lower, upper nad digits    @CharType tinyint )RETURNS TABLEAS RETURN -- An inline tally table with 1,000,000 rowsWITH E1(N) AS (SELECT N FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) V(N)),   -- 10     E2(N) AS (SELECt 1 FROM E1 a, E1 b), --100     E3(N) AS (SELECt 1 FROM E2 a, E2 b), --10,000     Tally(N) AS (SELECt ROW_NUMBER() OVER (ORDER BY @@SPID) FROM E3 a, E2 b) --1,000,000SELECt TOP(@Count)         n As Number,         CONCAt(@Prefix, (        SELECT  TOP (Length)      -- choose what char combination to use for the random part     CASE @CharType          WHEN 1 THEN Lower         WHEN 2 THEN Upper         WHEN 3 THEN IIF(Rnd % 2 = 0, Lower, Upper)         WHEN 4 THEN Digit         WHEN 5 THEN IIF(Rnd % 2 = 0, Lower, Digit)         WHEN 6 THEN IIF(Rnd % 2 = 0, Upper, Digit)         WHEN 7 THEN   CASE Rnd % 3      WHEN 0 THEN Lower      WHEN 1 THEN Upper      ELSE Digit  END     END        FROM Tally As t0          -- create a random number from the guid using the GuidGenerator view        CROSS APPLY (SELECt Abs(Checksum(NewGuid)) As Rnd FROM GuidGenerator) As rand        CROSS APPLY        ( -- generate a random lower-case char, upper-case char and digit SELECt  CHAr(97 + Rnd % 26) As Lower, -- Random lower case letter         CHAr(65 + Rnd % 26) As Upper,-- Random upper case letter         CHAr(48 + Rnd % 10) As Digit -- Random digit        ) As Chars        WHERe  t0.n <> -t1.n -- Needed for the subquery to get re-evaluated for each row        FOR XML PATH('')         ), @Suffix) As RandomStringFROM Tally As t1CROSS APPLY(    -- Select a random length between @MinLength and @MaxLength (inclusive)    SELECT TOP 1 n As Length    FROM Tally As t2    CROSS JOIN GuidGenerator     WHERe t2.n >= @MinLength    AND t2.n <= @MaxLength    AND t2.n <> t1.n    ORDER BY NewGuid) As Lengths;

最后，测试用例：

(l){30} - ahukoklijfahukokponmahukoahuko

SELECt RandomString FROM dbo.MaskGenerator(null, null, 30, 30, 2, 1);

结果：

1, eyrutkzdugogyhxutcmcmplvzofser2, juuyvtzsvmmcdkngnzipvsepviepsp

(d){7} - 7895623

SELECt RandomString FROM dbo.MaskGenerator(null, null, 7, 7, 2, 4);

结果：

1, 87444122, 2275313

(W){5} - ABCDE

SELECt RandomString FROM dbo.MaskGenerator(null, null, 5, 5, 2, 2);

结果：

1, RSYJE2, MMFAA

Test_Product_(d){1,4} - Test_Product_007

SELECt RandomString FROM dbo.MaskGenerator('Test_Product_', null, 1, 4, 2, 4);

结果：

1, Test_Product_9332, Test_Product_7

0.(d){2} - 0.59

SELECt RandomString FROM dbo.MaskGenerator('0.', null, 2, 2, 2, 4);

结果：

1, 0.682, 0.70

https://www.(l){10}.com - https://www.anything.com

SELECt RandomString FROM dbo.MaskGenerator('https://www.', '.com', 10, 10, 2, 1);

结果：

1, https://www.xayvkmkuci.com2, https://www.asbfcvomax.com

使用它来掩盖表内容的方法如下：

DECLARE @Count int = 10;SELECt  CAST(IntVal.RandomString As Int) As IntColumn,         UpVal.RandomString as UpperCasevalue,         LowVal.RandomString as LowerCasevalue,         MixVal.RandomString as MixedValue,        WithPrefix.RandomString As PrefixedValueFROM dbo.MaskGenerator(null, null, 3, 7, @Count, 4) As IntValJOIN dbo.MaskGenerator(null, null, 10, 10, @Count, 1) As LowVal    ON IntVal.Number = LowVal.NumberJOIN dbo.MaskGenerator(null, null, 5, 10, @Count, 2) As UpVal    ON IntVal.Number = UpVal.NumberJOIN dbo.MaskGenerator(null, null, 10, 20, @Count, 7) As MixVal    ON IntVal.Number = MixVal.NumberJOIN dbo.MaskGenerator('Test ', null, 1, 4, @Count, 4) As WithPrefix    ON IntVal.Number = WithPrefix.Number

结果：

IntColumn   UpperCasevalue  LowerCasevalue  MixedValue   PrefixedValue674         CCNVSDI         esjyyesesv      O2FAC7bfwg2Be5a91Q0     Test 493530732       UJKSLjktisddbnq      7o8B91Sg1qrIZSvG3AcL    Test 04669472     HDLJNBWPJ       qgtfkjdyku      xUoLAZ4pAnpn Test 826347       DNAKERR         vlehbnampb      NBv08yJdKb75ybhaFqED    Test 916084965     LJPMZMEU        ccigzyfwnf      MPxQ2t8jjmv0IT45yVcR    Test 46619851     FEHKGHTUW       wswuefehsp      40n7Ttg7H5YtVPF         Test 848781         LRWKVDUV        bywoxqizju      UxIp2O4Jb82TsTest 626852237       XXNPBL          beqxrgstdo      Uf9j7tCB4W2  Test 43876150      ZDRABW          fvvinypvqa      uo8zfRx07s6d0EP         Test 7

请注意，这是一个快速的过程-在我进行的测试中，生成具有5列的1000行平均不到半秒。