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
Declare @rstring varchar(10) Select @rstring = (SELECT [dbo].[fn_RandomString] (6,'0123456789')) Print @rString
Declare @rstring varchar(10) Select @rstring = (SELECT [dbo].[fn_RandomString] (3,'0123456789')) Print @rString
Looking for quality web hosting? Look no further than Arvixe Web Hosting!