Web Robot View of http://www.brettb.com/SQL_Help_Random_Strings.asp

Page Item Value
Title SQL Help: Generating Random Strings of Characters in Transact-SQL
Description This SQL Help article describes how to generate strings of random numbers, letters and other characters SQL Server's Transact-SQL
Keywords tutorial, example, sql server, tsql, transact sql, random, string, generate, generator, stored procedure, routine, function
Robots Meta Tag  
Page Content   HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | TOOLS | GALLERY | CONTACT Search: Go DEVELOPER TOOLS
ASP Doc Tool
ASP.NET Doc Tool
SQL Doc Tool
Index Server Companion
The Website Utility TECHNICAL ARTICLES
ASP
ASP.NET
JavaScript
Transact SQL

PHOTO GALLERIES
Canon EOS 300D Samples
Red Arrows 2004
Living Coasts
Web Page Backgrounds
More Galleries...

NEW STUFF
SQL Color Coder
Canon EOS 300D Samples
The Website Utility
Search Engine Optimisation
Build an ASP Search Engine
My Tropical Fishtank
Savings Other New Stuff...

POPULAR STUFF
Regular Expressions
ASP Documentation Tool
Index Server ASP
JavaScript Ad Rotator

LINKS
Business Website
ASPAlliance Articles

Home Articles Transact SQL Programming Articles

Generating Random Strings of Characters in Transact-SQL The following Transact SQL procedure can be used to generate a random string of characters. As such it can be used to for example generate a default password for a user. The specific characters that are used to generate the string can be specified, so it can be customised (e.g. to only create passwords of digits or lower cased letters). The length of the generated random string can also be specified.

It is recommended that this SQL procedure be used as a stored procedure .

Using as a Stored Procedure 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

Useful Links The SQL Documentation Tool builds technical documentation for Microsoft SQL Server databases. A free trial version is available for download. The ASP Documentation Tool can automatically document your ASP (VBScript and JScript) websites and SQL Server/Microsoft Access databases. An ASP.NET Documentation Tool is also available! Site Map

All content is © 1995 - 2006 Brett Burridge

Image Alt Tags Brettb.Com
Microsoft Certified Professional
Internal Links http://www.brettb.com/Default.asp (3 links in this page) [ Robot View of this URL ]
http://www.brettb.com/ASPDocumentationTool.asp (2 links in this page) [ Robot View of this URL ]
http://www.brettb.com/technicalwriting.asp (2 links in this page) [ Robot View of this URL ]
http://www.brettb.com/DeveloperTools.asp (2 links in this page) [ Robot View of this URL ]
http://www.brettb.com/SQL_Help.asp (2 links in this page) [ Robot View of this URL ]
http://www.brettb.com/TheWebsiteUtility.asp (2 links in this page) [ Robot View of this URL ]
http://www.brettb.com/JavaScriptArticles.asp [ Robot View of this URL ]
http://www.brettb.com/SQL_Help_Random_Strings.asp [ Robot View of this URL ]
http://www.brettb.com/Website_Search_Engine_Optimisation.asp [ Robot View of this URL ]
http://www.brettb.com/SearchResults.asp [ Robot View of this URL ]
http://www.brettb.com/js_banner_ad_rotator.asp [ Robot View of this URL ]
http://www.brettb.com/web.asp [ Robot View of this URL ]
http://www.brettb.com/CanonEOS300D_Gallery1.asp [ Robot View of this URL ]
http://www.brettb.com/BuildingAnASPSearchEngine.asp [ Robot View of this URL ]
http://www.brettb.com/backgrounds.asp [ Robot View of this URL ]
http://www.brettb.com/VBScriptRegularExpressions.asp [ Robot View of this URL ]
http://www.brettb.com/toc.asp [ Robot View of this URL ]
http://www.brettb.com/ASP.NETArticles.asp [ Robot View of this URL ]
http://www.brettb.com/Investments_ISAs.asp [ Robot View of this URL ]
http://www.brettb.com/TransactSQLColorCoder.asp [ Robot View of this URL ]
http://www.brettb.com/MyTropicalFishtank.asp [ Robot View of this URL ]
http://www.brettb.com/CanonEOS300D_Gallery3.asp [ Robot View of this URL ]
http://www.brettb.com/ASPWatchArticles.asp [ Robot View of this URL ]
http://www.brettb.com/Red_Arrows_2004.asp [ Robot View of this URL ]
http://www.brettb.com/Living_Coasts_Photos.asp [ Robot View of this URL ]
http://www.brettb.com/Gallery.asp [ Robot View of this URL ]
http://www.brettb.com/contact.asp [ Robot View of this URL ]
http://www.brettb.com/Biotechnology.asp [ Robot View of this URL ]
http://www.brettb.com/SearchingIndexServerWithASP.asp [ Robot View of this URL ]
http://www.brettb.com/ASPNetDocumentationTool.asp [ Robot View of this URL ]
http://www.brettb.com/gallery.asp [ Robot View of this URL ]
http://www.brettb.com/what's_new.asp [ Robot View of this URL ]
http://www.brettb.com/IndexServerCompanion.asp [ Robot View of this URL ]

Reporting Main Page

Report generated by The Website Utility 2.8