Генерация случайных строк в T-SQL: исключение символов

Быстрый ответ

Для генерации 10-символьной случайной строки в T-SQL следуйте этому примеру кода:

SELECT SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 10) AS RandomString

В данном коде функция NEWID() создаёт уникальный идентификатор, CONVERT преобразует его в строку (varchar), и с помощью SUBSTRING извлекается необходимое количество символов.

Если требуется идентификатор без тире, что заметно при использовании GUID, воспользуйтесь функцией REPLACE:

SELECT REPLACE(SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 13), '-', '') AS RandomString

В высоконагруженных системах предпочтительнее использовать функцию crypt_gen_random():

SELECT SUBSTRING(CONVERT(varchar(36), CRYPT_GEN_RANDOM(10)), 1, 10) AS RandomString

Более глубокое погружение в T-SQL

Генерация строки из конкретного набора символов

Для генерации строки из конкретного набора символов можно создать функцию T-SQL следующим образом:

CREATE FUNCTION dbo.GenerateRandomString(@Length int)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @characters NVARCHAR(62) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', 
            @output NVARCHAR(100) = ''
    WHILE LEN(@output) < @Length
    BEGIN
        SELECT @output = @output + SUBSTRING(@characters, CONVERT(int, RAND() * LEN(@characters)) + 1, 1)
    END
    RETURN @output
END

Далее функцию следует вызывать вот так:

SELECT dbo.GenerateRandomString(10) AS RandomString

Подробнее об этом расскажет наш спикер на видео

Исключение определённых символов

Чтобы исключить некоторые символы из получившейся строки, заключите функцию CHARINDEX в цикл:

WHILE CHARINDEX('-', @RandomString) > 0
BEGIN
    SET @RandomString = REPLACE(@RandomString, '-', '')
END

Усиление эффекта случайности

Силу случайности можно повысить, добавив в выражение создания случайных значений такую конструкцию:

SELECT SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 10) + 
       SUBSTRING(CONVERT(varchar(36), HASHBYTES('md5', CONVERT(varbinary, GETDATE()))), 1, 10)

Визуализация

Генерацию случайных строк в SQL можно представить с помощью метафоры с четырьмя барабанами:

• Барабан 1: [A-Z]
• Барабан 2: [a-z]
• Барабан 3: [0-9]
• Барабан 4: [Специальные символы]

Несколько вращений ручки 🕹️ и вот результат:

DECLARE @RandomString NVARCHAR(8);
SET @RandomString = 
    CHAR(ROUND((RAND() * 25) + 65, 0)) +  -- Большие буквы
    CHAR(ROUND((RAND() * 25) + 97, 0)) +  -- Маленькие буквы
    CHAR(ROUND((RAND() * 9) + 48, 0)) +   -- Цифры
    CHAR(ROUND((RAND() * 14) + 33, 0)) +  -- Спецсимволы
    ...;  -- Смешиваем до требуемой длины

В итоге получаем: 'Aa9$...'

Каждый запуск создаёт случайное сочетание символов с каждого барабана.

Оптимизация для массовой генерации

Когда требуется сгенерировать большое количество строк, необходимо оптимизировать производительность следующим образом:

INSERT INTO YourTable (RandomString)
SELECT TOP (1000000) 
       REPLACE(SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 13), '-', '') AS RandomString
FROM SomeLargeTable;

Воспроизводимость результатов

Если нужно повторно получить точно такой же набор строк, используйте функцию RAND с указанием сид для генератора случайных чисел:

SELECT CHAR(ROUND((RAND(12345) * 25) + 65, 0))

Создание идеальных строк

Настройка длины строки

Вы можете настроить длину строки в соответствии с вашими потребностями, используя SUBSTRING:

DECLARE @DesiredLength INT = 15;
SELECT SUBSTRING(CONVERT(varchar(36), NEWID()), 1, @DesiredLength) AS RandomString

Избегание ошибок

Ошибки округления могут существенно искажать конечный результат. Так что всегда проверяйте результаты:

SELECT LEN(RandomString), RandomString 
FROM (SELECT SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 10) AS RandomString) AS DerivedTable

Полезные материалы

1. NEWID (Transact-SQL) – SQL Server | Microsoft Learn
2. .net – Использование встроенных пространств имён в C# – Stack Overflow
3. Ручное переключение Availability Group в SQL Server
4. Построение трехмерных поверхностей – CodeProject