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.

[code language=”sql”]
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
[/code]

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

[code language=”sql”]
–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
[/code]

Examples of using this function.

[code language=”sql”]
Declare @rstring varchar(10)
Select @rstring = (SELECT [dbo].[fn_RandomString] (4,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’))
Print @rString
[/code]

Returns ‘EUGP’

[code language=”sql”]
Declare @rstring varchar(10)
Select @rstring = (SELECT [dbo].[fn_RandomString] (6,’0123456789′))
Print @rString
[/code]

Returns ‘076510

[code language=”sql”]
Declare @rstring varchar(10)
Select @rstring = (SELECT [dbo].[fn_RandomString] (3,’0123456789’))
Print @rString
[/code]

Returns ‘001’

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