Random alphanumeric generator
-
Hi, I need to create a 6 character random alphnumberic serial number. How can this be achieved in SQL Server 2000? I have been searching and found that Oracle has a function for this, so I am assuming there should be one for SQL then. I could do this easily in .net - but I dont want to have to keep sending a request to the database to check that this number is unique. I would prefer to do the whole thing in a SQL function. So far the only way I have been able to work out, is too create a new GUID - select 6 characters from it - then check that these are not already used in the table (otherwise create a new one and repeat) and insert it. Any help on this? Thanks Wayde
-
Hi, I need to create a 6 character random alphnumberic serial number. How can this be achieved in SQL Server 2000? I have been searching and found that Oracle has a function for this, so I am assuming there should be one for SQL then. I could do this easily in .net - but I dont want to have to keep sending a request to the database to check that this number is unique. I would prefer to do the whole thing in a SQL function. So far the only way I have been able to work out, is too create a new GUID - select 6 characters from it - then check that these are not already used in the table (otherwise create a new one and repeat) and insert it. Any help on this? Thanks Wayde
Ok, I found a way of doing this, may not be the best, but it what I thought of: DECLARE @Length INT, @Count INT, @RandNum INT, @RandomString VARCHAR(6), @IsValid BIT SELECT @RandomString = '', @Count = 0, @IsValid = 0, @Length = 6 WHILE (@IsValid = 0) BEGIN WHILE @Count < @Length BEGIN SET @RandNum = SUBSTRING(CAST(Rand() AS VARCHAR(20)), 3, 2) IF (((@RandNum > = ASCII('0')) And (@RandNum <= ASCII('9')) Or (@RandNum > = ASCII('A')) And (@RandNum < = ASCII('Z')))) BEGIN SET @RandomString = @RandomString + CHAR(@RandNum) SET @Count = @Count + 1 END END --Check here that the number is unique IF (SELECT certificateSerialNumber FROM Certificate WHERE certificateSerialNumber = @RandomString) IS Null BEGIN SET @IsValid = 1 END END SELECT @RandomString This cannot be used as a function though, due to the RandNumber generator. You can also emit the last part that checks if the value is unique. Change the @Length and @RandomString size to suit the size of the field that you need.