MSSQL - RandomString Function

26. May 2011 00:05

 

If you have ever tried to create a random string function it can be a little tricky. It is very easy to do this in sql or in a stored procedure however it is not possible to call the function RAND() from inside a function on mssql server. An example of this is below ..

 

CREATE FUNCTION TestRand()
RETURNS int
BEGIN
	DECLARE @ret int
	SELECT @ret = CONVERT(int, RAND() * 100)
	RETURN @ret
END

 

If you attempt to run the code above you are just going to get the following error.

Invalid use of a side-effecting operator 'rand' within a function.

 

There is actually a very easy way to work around this problem by simply moving the RAND function outside of the function. A really easy way todo this is to create a view for it. Like this

 

CREATE VIEW [dbo].[Random]
	AS SELECT RAND() AS RAND
GO

 

So by doing this we can avoid the error above.

 

CREATE FUNCTION TestRand()
RETURNS int
BEGIN
	DECLARE @ret int
	SELECT @ret = CONVERT(int, RAND*100) FROM Random
	RETURN @ret
END
GO

SELECT dbo.TestRand()

 

So now that we have an easy way to generate random data inside a function. We can work with this to generate random strings of data. Using the following function.

 

CREATE FUNCTION RandomString (@str AS varchar(MAX), @len AS int)
RETURNS varchar(MAX)
BEGIN
	DECLARE @newstr VARCHAR(MAX)
	DECLARE @counter int

	SET @newstr = ''
	SET @counter = 0
	
	WHILE @counter < @len
	BEGIN
		
		SELECT @newstr = @newstr + SUBSTRING(@str, (SELECT CONVERT(int, (RAND * LEN(@str) + 1)) FROM Random), 1)
		SET @counter = @counter + 1
	END
		
	RETURN @newstr
END
GO

 

I am sure you are wondering what is the point in this. Well that is simple if you have ever attempted to generate specific test data in a stored procedure you end up doing a few loops a lot of function calls and repeating a lot of code. Now that the functions above are working using a tally table we can generate as much data as we will ever need! Here is an example ..

 

INSERT INTO Customer (FirstName, LastName, EMail)
	SELECT TOP 1000
		dbo.RandomString('abcdefghijklmnopqrstuvwxyz', 25) AS FirstName,
		dbo.RandomString('abcdefghijklmnopqrstuvwxyz', 25) AS LastName,
		dbo.RandomString('abcdefghijklmnopqrstuvwxyz', 6) + '@' + dbo.RandomString('abcdefghijklmnopqrstuvwxyz', 5) + '.local' AS EMail
			FROM Tally

Its simple, short, readable and easy to ajust to various database layouts.

E-mail Kick it! DZone it! del.icio.us Permalink


Pingbacks and trackbacks (1)+