HOWTO: Generate a Random String of Characters in SQL

Here's a stored procedure to generate a string of random characters. This is useful when, for example, one needs to generate a random password. Please excuse the poor formatting, I am working on finding and implementing a decent code formatted for both SQL and C#.

Thanks to Raymond Lewallen over at CodeBetter.com


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Raymond Lewallen
--                    http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/17/63281.aspx
-- Create date: 2005/05/16
-- Description: Generate a random string of
--                            given length.
--                        : Everything is self-explanatory.
--                        : Right now max length is set to
--                            100. So anything between 1 and
--                            100 will work for a length.
--                        : If you specify a
--                            @charactersToUse, the bit flags
--                            get ignored.
--                        : All spaces are stripped from the
--                            @charactersToUse.
--                        : Characters can repeat. Will be
--                            handled in a future version.
--
-- Modifications:
-- Author:            A. Jackson
-- Create date: 2010/04/21
-- Description: Renamed the sp to fit into the
--                            naming convention of the
--                            database.
--                        : Formatted the header.
--                        : Reordered the parameters so that
--                            the @toReturn OUT parameter is
--                            first.
--                        : Added @useSpecialCharacters BIT.
--                        : Formatted the code.
--                        : Added default parameter values.
-- =============================================
CREATE PROCEDURE [dbo].[spGenerateRandomString]
        (
            @toReturn VARCHAR(100) OUT,
            @useSpecialCharacters BIT = 1,
            @useNumbers BIT = 1,
            @useLowerCase BIT = 1,
            @useUpperCase BIT = 1,
            @charactersToUse VARCHAR(100) = '',
            @stringLength INT = 100
        )
AS
BEGIN
        IF @stringLength <= 0  
        BEGIN
                RAISERROR ( 'Cannot generate a random string of zero length.', 16, 1 )
        END

        DECLARE @characters VARCHAR(110)
        DECLARE @count INT

        SET @characters = ''

        IF @useSpecialCharacters = 1
        BEGIN
        SET @count = 33
        WHILE @count <= 38
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 40
        WHILE @count <= 47
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 58
        WHILE @count <= 64
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 91
        WHILE @count <= 95
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 123
        WHILE @count <= 126
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
    END
    
        IF @useNumbers = 1  
        BEGIN
                -- load up numbers 0 - 9
                SET @count = 48
                WHILE @count <= 57
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
        END
        
        IF @useLowerCase = 1  
        BEGIN
        -- load up uppercase letters A - Z
        SET @count = 65
        WHILE @count <= 90
        BEGIN
                SET @characters = @characters +
                        CAST(CHAR(@count) AS CHAR(1))
                SET @count = @count + 1
        END
        END

        IF @useUpperCase = 1  
        BEGIN
                -- load up lowercase letters a - z
                SET @count = 97
                WHILE @count <= 122
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
        END

        SET @count = 0
        SET @toReturn = ''

        -- If you specify a character set to use, the bit flags get ignored.
        IF LEN(@charactersToUse) > 0  
        BEGIN
                WHILE CHARINDEX(@charactersToUse, ' ') > 0
                BEGIN
                        SET @charactersToUse = REPLACE(@charactersToUse, ' ', '')
                END

                IF LEN(@charactersToUse) = 0  
                BEGIN
                        RAISERROR ( 'Cannot use an empty character set.', 16, 1 )
                END

                WHILE @count <= @stringLength
                BEGIN
                        SET @toReturn = @toReturn + SUBSTRING(@charactersToUse,
                                                                                                    CAST(ABS(CHECKSUM(NEWID())) *
                                                                                                    RAND(@count) AS INT) %
                                                                                                    LEN(@charactersToUse) +
                                                                                                    1, 1)
                        SET @count = @count + 1
                END
        END
        ELSE  
        BEGIN
                WHILE @count <= @stringLength
                BEGIN
                        SET @toReturn = @toReturn + SUBSTRING(@characters,
                                                                                                    CAST(ABS(CHECKSUM(NEWID())) *
                                                                                                    RAND(@count) AS INT) %
                                                                                                    LEN(@characters) + 1,
                                                                                                    1)
                        SET @count = @count + 1
                END
        END
END

GO

Comments

Post a Comment

Popular posts from this blog

Google South Africa's Telephone Number

Are you tired of searching for Google South Africa's contact details? You are pretty sure that their office is on the corner of Main Road and Sloane Street , but more than that, you just don't know. Well, @StewartMoss managed to dig up their telephone number - after first having to call Google UK and Google Kenya! So, directly from the Kenyan horse's mouth, here it is: Google South Africa's telephone number: +27-11-575-6000 Apparently, according to the receptionist, they "don't accept calls." So, there you go. You now have a telephone number for Google South Africa. I hope that you have better luck being put through to talk to someone than Stewart did. Thank you for this information and for calling Google UK, Kenya and South Africa Stewart, you rock!
Read more

A Way to Handle Raised Errors in Both Dynamic and Static SQL Code

If you've needed a reliable way to handle errors that might be raised in both dynamic and static SQL code, here is what I've been doing: SET   ANSI_NULLS   ON GO SET   QUOTED_IDENTIFIER   ON GO -- ============================================= -- Author:      Erland Sommarskog - http://www.sommarskog.se/error_handling_2005.html -- Create date: 2009/11/29 -- Description: **Call this sp from within your --              CATCH block** This --              reraises/throws the most recent --              error. Please see --              http://w...
Read more

(It's Been a Long Time Coming... 🎵🎹) Getting Date Quarters for Months in Python - Or "Why Date and Time Dimension Tables Exist"

Image
Hi. It's been a while. :-) So I recently saw a question on StackOverflow called " First month of quarter given month in Python " where they were asking how to work out a date related issue in Python. There are some wonderful techincal and mathematical solutions there, but it reminded me very much of proper architecture design, especially when the project will be for a non-hobby. User jonrsharpe nailed it with his answer , IMHO. In the continued balancing act of storage space vs. compute power, a dataset will usually win over a computation. Not always, just usually. And even when it doesn't, depending on circumstances, a shared dataset is VITAL to avoid computation bugs when it is needed more than a few times, or by more than one department. It can be a lifesaver when it comes to reporting, as well as Fact-Dimension architecture or even a Data Vault architecture. Create, share, and maintain the single version of the truth. Here's a code sam...
Read more