Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - Generate Random Strings

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:     Rafal Skotak
Date:        14 Jan 2008

Description

Generates a list of random varchar(8) strings.  
Currently only includes alphabetic characters, but could be extended to include all aphanumeric characters, which may make it usable as a random password generator.
Otherwise, it could be used to generate test data. 
 

Code

 

 /************************************************************************
*
* Author Rafal Skotak
* Purpose Procedure generates a list of random varchars
* Date 2008-01-14
*
************************************************************************/


IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID('dbo.proc_random_varchars') AND xtype = 'P')
  
DROP PROCEDURE dbo.proc_random_varchars
GO

CREATE PROCEDURE dbo.proc_random_varchars
  
@count INT = NULL
AS
BEGIN
   SET NOCOUNT ON

  
----------------------------------------------------------
   -- check parameters

  
IF @count IS NULL
  
SET @count = 1024

  
IF @count < 1 OR @count > 999999
  
BEGIN
       RAISERROR
('Invalid @count value', 16, 1)
      
RETURN
   END

  
----------------------------------------------------------
   --

  
CREATE TABLE #temp_result_table
  
(
      
rec_id INT IDENTITY(1, 1) PRIMARY KEY,
      
value VARCHAR(8) NOT NULL
   )

  
WHILE @count > 0
  
BEGIN
       INSERT INTO
#temp_result_table (value) VALUES
      
(
      
CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A'))) +
      
CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A'))) +
      
CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A'))) +
      
CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A'))) +
      
CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A'))) +
      
CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A'))) +
      
CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A'))) +
      
CHAR(FLOOR(RAND() * 26) + ASCII('A') + FLOOR(RAND() * 2) * (ASCII('a') - ASCII('A')))
       );

      
SET @count = @count - 1
  
END

   SELECT
* FROM #temp_result_table

  
DROP TABLE #temp_result_table
END
GO

--- example:

EXEC dbo.proc_random_varchars 128


ċ
Generate random strings.sql
(2k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments