Generate Jumbled/Randomized Words Using Custom SQL Function

To fill up my database with test data, I needed to generate dummy text, phone numbers, social security numbers etc. There are data generators out there, but i wanted to write my own, so here it is...

ALTER FUNCTION dbo.fn_WORD_JUMBLER(

      @Text VARCHAR(MAX) = '',

      @DataType INT = 0, -- 0 = random text, 1 = random number

      @DataLenth INT = 10    

)

RETURNS VARCHAR(MAX)

AS

BEGIN

/*

USAGE:

SELECT dbo.fn_word_jumbler('ankeet Is Awesome!', DEFAULT, DEFAULT)

SELECT dbo.fn_word_jumbler('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ', DEFAULT, DEFAULT)

-- ALPHABETS

SELECT dbo.fn_word_jumbler(DEFAULT, 0, DEFAULT)

-- NUMBERS

SELECT dbo.fn_word_jumbler(DEFAULT, 1, DEFAULT)

=============================================================================

Created By: Ankeet Shah

Purpose: Accepts an input and Returns the same text in reorganized/jumbled/randomized order. Cannot 'de-randomize'.

This function can also be repurposed to be used as random data generator to fill up tables.

*/

            IF @Text = ''

            BEGIN

                  -- set defaults

                  IF @DataType = 0

                        SET @Text = left(replICATE('ABCDEFGHIJKLMNOPQRSTUVWXYZ',100),@DataLenth)

                       

                  IF @DataType = 1

                        SET @Text = left(replICATE('12345678991234567893123456',100),@DataLenth)                 

           

            END           

           

            DECLARE @Jumbled VARCHAR(MAX) = ''

           

            DECLARE @TextLen as INT,

                        @i as INT = 1,

                        @Pick as INT

                       

            DECLARE @Randomize AS TABLE (num INT, id VARCHAR(100))

            SET @TextLen = LEN(@Text)          

            -- Build the table using GetNewID view which compensates for the inability to use newid() in functions.

            WHILE @i <= @TextLen

            BEGIN

                  INSERT INTO @Randomize

                  SELECT @i, NEW_ID

                  FROM dbo.GetNewID

                  SET @i = @i + 1

            END

            SET @i = 1

            -- Run through loop for as many times as is the length of the plain text.

            -- @Randomize table will make sure that characters from the plain text are returned at random.

            WHILE @i <= LEN(@Text)

            BEGIN

                  SELECT @Pick = (SELECT top 1 num FROM @Randomize ORDER BY id)

                 

                  SELECT @Jumbled = @Jumbled + SUBSTRING(@text,@Pick,1)

                 

                  -- remove the number from stack to avoid any duplication

                  DELETE @Randomize WHERE num = @Pick

                  SET @i = @i + 1

            END

            RETURN @Jumbled

 END

MS SQL does not allow the use if NEWID() function within UDFs. An intelligent way out of it is to create a view that does the job for you.

CREATE VIEW dbo.GetNewID

AS

SELECT NEWID() as ID

Keep this function in your back pocket, and you will find it useful at some point!