Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Generate Random String

Posted on 2010-11-15
4
1,076 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
ID: 34135970
select left(cast(newid() as varchar(36)),6)
0
 
LVL 14

Assisted Solution

by:leoahmad
leoahmad earned 100 total points
ID: 34135975
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
ID: 34153541
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
ID: 34182918
Thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

789 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