Generating Random Strings in SQL

The other day I was requested to add records to a lookup table only if the description being added didn’t exist. This seemed easy to do since I could run a select against a table and use NOT EXIST. However; I quickly realized that once I got the result of the select I would need to add a record, I had to create a unique alpha character to be used in the table as a primary key (could not use integer). So how do you create a random character that is unique? After looking at several options I came across a SQL forum that helped me in creating a function that helps.

Unfortunately you cannot use RAND() in SQL Function because that would make the function non-deterministic. However; you can trick the UDF to accept a random number by using a View.

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_RandomNewID]'))
BEGIN
DROP VIEW [dbo].[vw_RandomNewID];
END
GO
CREATE VIEW [dbo].[vw_RandomNewID]
AS
SELECT NEWID() AS [NewID]
GO

With this out of the way then you can create this function.

--Creates the Function to randomly generate string
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fn_RandomString]') AND type IN (N'FN', N'FS', N'FT', N'TF', N'IF'))
BEGIN
DROP FUNCTION [dbo].[fn_RandomString];
END
GO

Create FUNCTION fn_RandomString(@length tinyint = 8,@ValidCharacters varchar(50))
	RETURNS varchar(255)
AS
BEGIN
--Generates a random Number
--Set the Multiplier times the number of Characters you are using
declare @alphabet varchar(36)
DECLARE @rstring varchar(15)
Declare @counter int
Declare @CharacterLocation int

--Place the Valid Characters that can be returned randomly
  SET @counter = 1
	SET @rstring = ''
	WHILE @counter <= @length
	BEGIN
	  SET @counter = @counter + 1
    SELECT @CharacterLocation = ABS(CAST(CAST([NewID] AS VARBINARY) AS INT)) %
        LEN(@ValidCharacters) + 1
        FROM [dbo].[vw_RandomNewID]

     SET @rstring = @rstring + substring(@ValidCharacters, @CharacterLocation, 1)
    --Print @rstring
  End
	RETURN (@rstring)
End
Go

Examples of using this function.

	Declare @rstring varchar(10)
    Select @rstring = (SELECT [dbo].[fn_RandomString] (4,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))
	Print @rString

Returns ‘EUGP’

	Declare @rstring varchar(10)
    Select @rstring = (SELECT [dbo].[fn_RandomString] (6,'0123456789'))
	Print @rString

Returns ’076510

	Declare @rstring varchar(10)
    Select @rstring = (SELECT [dbo].[fn_RandomString] (3,'0123456789'))
	Print @rString

Returns ’001′

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Comments are closed.