Generate Random String

In SQL Server, I need to generate a string (6 char length) that contain only alpha numeric and numeric character.

Such as : 3AC52C, or 308E2E or F30EDC

The string at least has 1 alpha numeric character and at least 1 numeric character.

Please help. Thanks.
softblessAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chapmandewConnect With a Mentor Commented:
select left(cast(newid() as varchar(36)),6)
0
 
Muhammad Ahmad ImranConnect With a Mentor Database DeveloperCommented:
The following stored procedure creates a random string of characters of a length specified by the parameter @Length:

CREATE PROCEDURE sp_GeneratePassword
(
    @Length int
)

AS

DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''

SET NOCOUNT ON

SET @counter = 1

WHILE @counter < (@Length + 1)

BEGIN

        SET @RandomNumber = Rand()
        SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))

        SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)

        SET @counter = @counter + 1

        SET @RandomID = @RandomID + @CurrentCharacter

END

SELECT @RandomID AS 'Password'
GO

Open in new window

0
 
lomo74Connect With a Mentor Commented:
try this

with cte as (
	select
	case when rand() >= 0.5
		then char(65 + cast(rand() * 255 as int) % 26) -- letter
		else char(48 + cast(rand() * 255 as int) % 10) -- digit
	end +
	case when rand() >= 0.5
		then char(65 + cast(rand() * 255 as int) % 26)
		else char(48 + cast(rand() * 255 as int) % 10)
	end +
	case when rand() >= 0.5
		then char(65 + cast(rand() * 255 as int) % 26)
		else char(48 + cast(rand() * 255 as int) % 10)
	end +
	case when rand() >= 0.5
		then char(65 + cast(rand() * 255 as int) % 26)
		else char(48 + cast(rand() * 255 as int) % 10)
	end +
	case when rand() >= 0.5
		then char(65 + cast(rand() * 255 as int) % 26)
		else char(48 + cast(rand() * 255 as int) % 10)
	end +
	case when rand() >= 0.5
		then char(65 + cast(rand() * 255 as int) % 26)
		else char(48 + cast(rand() * 255 as int) % 10)
	end as pwd
)
-- ensure at least one digit and one letter,
-- since we can have obtained things like 123456 or ABCDEF
select	case
			when pwd not like '%[0-9]%' then left(char(48 + cast(rand() * 255 as int) % 10) + pwd, 6) -- prepend at least one digit if no digit present
			when pwd not like '%[A-Z]%' then left(char(65 + cast(rand() * 255 as int) % 26) + pwd, 6) -- prepend at least one letter if no letter present
			else pwd -- ok, pwd already composed of letters + digits
		end
from	cte

Open in new window

0
 
softblessAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.