Tag: sql

In my previous random SQL tricks post, I discussed how to generate random alphanumeric strings of any length.  A slight variation on that idea that also proved useful in generating test data is the following stored procedure (which generates a varchar consisting entirely of numbers):

CREATE PROCEDURE [dbo].[SpGenerateRandomNumberString]
@randomString varchar(15) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ”

WHILE @counter <= 15
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (57-48+1)*RAND()))

SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
GO

The range in the select for @nextChar maps to ASCII values for the digits 0-9.  Unlike the stored procedure from my first post, there’s no if statement to determine whether or not the random value retrieved is allowed because the ASCII range for digits is contiguous.  The needs of my application restricted the length of this numeric string to 15 characters.  For more general use, the first refactoring would probably add string length as a second parameter, so the numeric string could be a variable length.

One of my most recent tasks at work has been generating test data for integration tests of a new application.  We don’t have the version of Visual Studio which does it for you, and rather than write an app that did it, I spent the past week hunting for examples that just used Transact-SQL.  The initial post that I found the most useful is this one, in which the author provides five different ways of generating random numbers.  I use his third method quite often, as you’ll see in this post (and any others I write on this topic).

One of our needs for random test data was alphanumeric strings of varying lengths.  Because the content of the text mattered less than the need for text, it didn’t have to resemble actual names (or anything recognizable).  The first example I found of a T-SQL stored procedure for generating a random string was in this blog post by XSQL Software.  The script does generate random strings, but they include non-alphanumeric characters.  To get the sort of random strings I wanted, I took the random number generation method from the first post and the stored procedure mentioned earlier and adapted them to this:

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ”

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

The range in the select for @nextChar is the set of ASCII table values that map to digits, upper-case letters, and lower-case letters (among other things).  The “if” branch values in the set are those ASCII table values that map to punctuation, brackets, and other non-alphanumeric characters.  Only alphanumeric characters are added to @randomString as a result.  Having a stored procedure like this one available makes it much easier to generate test data, especially since it can be called from other stored procedures.