Filter out "special" characters from CRYPT_GEN_RANDOM()

sql-server-2008

Solution 1:

Borrowing from @JNK's idea a bit. Assuming you really are after alphanumeric only, that case doesn't matter, and the longest string you'll need is 36 characters, you can first create a view:

CREATE VIEW dbo.RetrieveNewID
AS
    SELECT [NewID] = NEWID();
GO

Then create this function:

CREATE FUNCTION dbo.GenerateRandomNumbersLetters
(
    @NumberOfCharacters TINYINT
)
RETURNS VARCHAR(32)
AS
BEGIN
    RETURN 
    (
        SELECT LEFT(REPLACE([NewID], '-', ''), @NumberOfCharacters)
        FROM dbo.RetrieveNewID
    );
END
GO

My results:

SELECT r = dbo.GenerateRandomNumbersLetters(4);
SELECT r = dbo.GenerateRandomNumbersLetters(4);
SELECT r = dbo.GenerateRandomNumbersLetters(4);

r
----
EA93
9D32
B229

This will be ok if you are doing this in onesy-twosy mode. If you are trying to generate this function for a set, it will get less efficient as the set gets larger. Example:

SELECT r = dbo.GenerateRandomNumbersLetters(i) FROM
(
    SELECT i = 1 
    UNION ALL SELECT i = 2 
    UNION ALL SELECT i = 3
) AS x;

I don't expect that to perform particularly well on a large data set.

Now, if you want to use characters that are outside of the set possible in a GUID (A-Z/0-9), you could do this with a table. Assuming the same view above, we can create a table and stuff it with whatever characters we want to be available to the random generator (which includes characters outside of strict alphanumeric, but can still exclude any "special" characters you don't want to consider).

CREATE TABLE dbo.RandomCharacters
(
    Digit NVARCHAR(1) COLLATE Latin1_General_CS_AI PRIMARY KEY
);

;WITH x AS 
(
    SELECT TOP (200) i = ROW_NUMBER() OVER (ORDER BY [object_id])
    FROM sys.all_objects
    ORDER BY [object_id]
)
INSERT dbo.RandomCharacters 
SELECT NCHAR(i) FROM x
WHERE (i BETWEEN 65 AND 90)  -- A-Z
   OR (i BETWEEN 97 AND 122) -- a-z
   OR (i BETWEEN 48 AND 57)  -- 0-9
   OR (i IN (42, 126, 181)); -- *, ~, µ

Now, the function that does the magic (again this all hinges on the trick that allows us to reference NEWID() in a function by hiding it in a view):

CREATE FUNCTION dbo.GenerateRandomCharacters
(
    @NumberOfCharacters TINYINT
)
RETURNS NVARCHAR(255)
AS
BEGIN
    RETURN
    (
        SELECT (SELECT x.Digit FROM (
            SELECT TOP (@NumberOfCharacters) r.Digit 
            FROM dbo.RandomCharacters AS r
            CROSS JOIN dbo.RetrieveNewID AS d
            ORDER BY CONVERT(VARBINARY(36), d.[NewID])
        ) AS x
        FOR XML PATH(''), TYPE).value(N'./text()[1]',N'nvarchar(max)')
    );
END
GO

My results:

SELECT r = dbo.GenerateRandomCharacters(4);
SELECT r = dbo.GenerateRandomCharacters(4);
SELECT r = dbo.GenerateRandomCharacters(4);

r 
----
H~1r
Dfn2
µHxF

In this latter solution, duplicates are not supported. Nor do I expect performance to be spectacular on large data sets. But you can have more than 32 characters. :-)

Filter out "special" characters from CRYPT_GEN_RANDOM()

Solution 1:

Related

Recent Posts