Solved

Generate Random String

Posted on 2010-11-15
4
1,072 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:softbless
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
Comment Utility
select left(cast(newid() as varchar(36)),6)
0
 
LVL 14

Assisted Solution

by:leoahmad
leoahmad earned 100 total points
Comment Utility
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
 
LVL 8

Assisted Solution

by:lomo74
lomo74 earned 100 total points
Comment Utility
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
 

Author Closing Comment

by:softbless
Comment Utility
Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now