Source Code for http://www.brettb.com/SQL_Help_Random_Strings.asp
<html>
<head>
<title>SQL Help: Generating Random Strings of Characters in Transact-SQL</title>
<meta name="description"
content="This SQL Help article describes how to generate strings of random numbers, letters and other characters SQL Server's Transact-SQL">
<meta name="keywords"
content="tutorial, example, sql server, tsql, transact sql, random, string, generate, generator, stored procedure, routine, function">
<link REL="stylesheet" HREF="BrettbDotCom.css" TYPE="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
</head>
<body>
<script language="JavaScript">
function SubmitHeaderSearchForm() {
document.FormSubmitHeaderSearch.submit();
}
</script>
<div align="center"><table border="0" cellpadding="0" cellspacing="0" width="800"
style="border: 1px solid rgb(0,0,0)">
<tr>
<td width="167"><a href="Default.asp" title="Brettb.Com (Back to Home Page)"><img src="Images/BrettbDotCom_Logo.jpg" width="167" height="72"
alt="Brettb.Com" border="0"></a></td>
<td width="633"><img src="HeaderImages/PurpleFlame.jpg" width="633" height="72" alt=""></td>
</tr>
</table><table border="0" cellpadding="0" width="802" bgcolor="#DD3333"
style="border-left: 1px solid rgb(0,0,0); border-right: 1px solid rgb(0,0,0); border-top: 1px none rgb(0,0,0); border-bottom: 1px solid rgb(0,0,0)">
<tr>
<td class="TDHeader" valign="middle" align="left"> <a href="Default.asp"
title="HOME">HOME</a> | <a href="web.asp" title="ABOUT ME">ABOUT ME</a> | <a
href="Biotechnology.asp" title="BIOTECHNOLOGY">BIOTECHNOLOGY</a> | <a
href="technicalwriting.asp" title="ARTICLES">ARTICLES</a> | <a
href="DeveloperTools.asp" title="DEVELOPER TOOLS">TOOLS</a> | <a href="Gallery.asp"
title="GALLERY">GALLERY</a> | <a href="contact.asp" title="CONTACT">CONTACT</a></td>
<form method="POST" action="SearchResults.asp" name="FormSubmitHeaderSearch"><td align="right" class="TDHeader" valign="middle">Search: <input type="text" name="query"
size="20" maxlength="100"> <a href="javascript:SubmitHeaderSearchForm();">Go</a></td></form>
</tr>
</table>
</center></div>
<div align="center"><center>
<table border="0" cellpadding="8" cellspacing="0" width="802"
style="border-left: 1px solid rgb(0,0,0); border-right: 1px solid rgb(0,0,0)">
<tr>
<td bgcolor="#E0E0E0" width="151" valign="top" align="left" class="TDLeftPanel" nowrap><strong><a href="DeveloperTools.asp" title="DEVELOPER TOOLS">DEVELOPER TOOLS</a><br>
</strong> <a href="ASPDocumentationTool.asp" title="ASP Documentation Tool">ASP Doc
Tool</a><br>
<a href="ASPNetDocumentationTool.asp" title="ASP.NET Documentation Tool">ASP.NET Doc
Tool</a><br>
<a href="http://www.winnershtriangle.com/w/Products.SQLDocumentationTool.asp" title="SQL Documentation Tool" target="_blank">SQL Doc
Tool</a><br>
<a href="IndexServerCompanion.asp" title="Index Server Companion">Index Server
Companion</a><br>
<a href="TheWebsiteUtility.asp" title="The Website Utility">The Website Utility</a><p><strong>TECHNICAL
ARTICLES <br>
</strong> <a href="ASPWatchArticles.asp" title="ASP Articles">ASP</a><br>
<a href="ASP.NETArticles.asp" title="ASP.NET Articles">ASP.NET</a><br>
<a href="JavaScriptArticles.asp" title="JavaScript Articles">JavaScript</a><br>
<a href="SQL_Help.asp" title="Transact SQL Articles">Transact SQL</a></p>
<p><strong>PHOTO GALLERIES</strong><br>
<a href="CanonEOS300D_Gallery1.asp" title="Canon EOS 300D Samples">Canon EOS 300D
Samples</a><br>
<a href="Red_Arrows_2004.asp" title="Red Arrows 2004">Red Arrows 2004</a><br>
<a href="Living_Coasts_Photos.asp" title="Living Coasts">Living Coasts</a><br>
<a href="backgrounds.asp" title="Web Page Backgrounds">Web Page Backgrounds</a><br>
<a href="gallery.asp" title="More Galleries...">More Galleries...</a></p>
<p><strong>NEW STUFF</strong><br>
<a href="TransactSQLColorCoder.asp" title="Transact SQL Color Coder">SQL Color Coder</a><br>
<a href="CanonEOS300D_Gallery3.asp" title="Canon EOS 300D Samples">Canon EOS 300D
Samples</a><br>
<a href="TheWebsiteUtility.asp" title="The Website Utility">The Website Utility</a><br>
<a href="Website_Search_Engine_Optimisation.asp" title="Useful Search Engine Optimization Techniques">Search Engine Optimisation</a><br>
<a href="BuildingAnASPSearchEngine.asp" title="Creating an ASP Search Engine for your Website">Build an ASP Search Engine</a><br>
<a href="MyTropicalFishtank.asp" title="My Tropical Freshwater Fishtank">My Tropical Fishtank</a><br>
<a href="Investments_ISAs.asp" title="Savings & Investments">Savings & Investments</a><br>
<a href="what's_new.asp" title="What's New?">Other New Stuff...</a></p>
<p><strong>POPULAR STUFF</strong><br>
<a href="VBScriptRegularExpressions.asp" title="VBScript Regular Expressions">Regular
Expressions</a><br>
<a href="ASPDocumentationTool.asp">ASP Documentation Tool</a><br>
<a href="SearchingIndexServerWithASP.asp" title="Index Server & ASP">Index
Server & ASP</a><br>
<a href="js_banner_ad_rotator.asp" title="JavaScript Banner Ad Rotator">JavaScript Ad Rotator</a></p>
<p><strong>LINKS</strong><br>
<a href="http://www.winnershtriangle.com/w/Default.asp" title="Business Website"
target="_blank">Business Website</a><br>
<a href="http://authors.aspalliance.com/brettb/" title="ASPAlliance Articles"
target="_blank">ASPAlliance Articles</a><br>
</p>
<p><img alt="Microsoft Certified Professional"
src="images/MCP_c_smaller.gif" width="151" height="38"></p>
<p align="center">
<script type="text/javascript"><!--
google_ad_client = "pub-7044749527879330";
google_alternate_color = "FFFFFF";
google_ad_width = 120;
google_ad_height = 90;
google_color_border = "000000";
google_color_bg = "DDDDDD";
google_color_link = "FF0000";
google_color_url = "DD3333";
google_color_text = "CCCCCC";
google_ad_format = "120x90_0ads_al_s";
google_ad_channel ="6714185213";
//--></script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</p>
<p align="center">
<SCRIPT type='text/javascript' language='JavaScript' src='http://xslt.alexa.com/site_stats/js/s/a?amzn_id=brettbcom&url=www.brettb.com'></SCRIPT>
</p>
</td>
<td width="551" valign="top" align="left">
<p><a href="Default.asp" title="Home">Home</a> > <a href="technicalwriting.asp"
title="Articles">Articles</a> > <a href="SQL_Help.asp"
title="Transact SQL Programming Articles">Transact SQL Programming Articles</a></p>
<h1>Generating Random Strings of Characters in Transact-SQL</h1>
<p>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.</p>
<p>It is recommended that this SQL procedure be used as a <a href="#Stored_Procedure">stored
procedure</a>.</p>
<h2><a name="Stored_Procedure"></a>Using as a Stored Procedure</h2>
<p>The following stored procedure creates a random string of characters of a length
specified by the parameter @Length:</p>
<p><small><font color="#000080">CREATE PROCEDURE sp_GeneratePassword<br>
(<br>
@Length int<br>
)<br>
<br>
AS<br>
<br>
DECLARE @RandomID varchar(32)<br>
DECLARE @counter smallint<br>
DECLARE @RandomNumber float<br>
DECLARE @RandomNumberInt tinyint<br>
DECLARE @CurrentCharacter varchar(1)<br>
DECLARE @ValidCharacters varchar(255)<br>
SET @ValidCharacters = '</font><font color="#FF0000">ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$</font><font
color="#000080">'<br>
DECLARE @ValidCharactersLength int<br>
SET @ValidCharactersLength = len(@ValidCharacters)<br>
SET @CurrentCharacter = ''<br>
SET @RandomNumber = 0<br>
SET @RandomNumberInt = 0<br>
SET @RandomID = ''<br>
<br>
SET NOCOUNT ON<br>
<br>
SET @counter = 1<br>
<br>
WHILE @counter < (@Length + 1)<br>
<br>
BEGIN<br>
<br>
SET @RandomNumber = Rand()<br>
SET @RandomNumberInt = Convert(tinyint,
((@ValidCharactersLength - 1) * @RandomNumber + 1))<br>
<br>
SELECT @CurrentCharacter =
SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)<br>
<br>
SET @counter = @counter + 1<br>
<br>
SET @RandomID = @RandomID + @CurrentCharacter<br>
<br>
END<br>
<br>
SELECT @RandomID AS 'Password'<br>
GO</font></small></p>
<font SIZE="2">
<h2>Useful Links</h2>
<ul>
<li>The <strong><a
href="http://www.winnershtriangle.com/w/Products.SQLDocumentationTool.asp" target="_blank" title="SQL Documentation Tool">SQL
Documentation Tool</a></strong> builds technical documentation for Microsoft SQL Server
databases. A free trial version is available for download.</li>
</ul>
<ul>
<li>The <a href="http://www.winnershtriangle.com/w/Products.ASPDocumentationTool.asp"
title="ASP Documentation Tool">ASP Documentation Tool</a> can automatically document your
ASP (VBScript and JScript) websites and SQL Server/Microsoft Access databases. An <a
href="http://www.winnershtriangle.com/w/Products.ASPNETDocumentationTool.asp">ASP.NET
Documentation Tool</a> is also available!</li>
</ul>
<p>
</td>
<td width="100" valign="top" align="left">
<script type="text/javascript"><!--
google_ad_client = "pub-7044749527879330";
google_ad_width = 120;
google_ad_height = 600;
google_ad_format = "120x600_as";
google_ad_channel ="9212633149";
google_ad_type = "text_image";
google_color_border = "CCCCCC";
google_color_bg = "FFFFFF";
google_color_link = "0000FF";
google_color_url = "DD3333";
google_color_text = "000000";
google_alternate_ad_url = "http://www.brettb.com/NoGoogleAds.asp";
//--></script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
<iframe marginwidth="0" marginheight="0" src="http://rcm.amazon.com/e/cm?t=brettbcom&o=1&p=10&l=st1&mode=books&search=sql server&=1&fc1=&lc1=<1=&bg1=&f=ifr" width="130" height="460" border="0" frameborder="0" style="border:none;" scrolling="no"></iframe>
</td>
</tr>
</table>
</center></div>
<div align="center"><center>
<table border="0" cellpadding="2" cellspacing="0" width="802"
style="border: 1px solid rgb(0,0,0)">
<tr>
<td class="TDFooter"> <a href="toc.asp" title="Site Map">Site Map</a></td>
<td class="TDFooter"><p align="right">All content is © 1995 - 2006 Brett Burridge</td>
</tr>
</table>
</center></div> </p>
</font>
</body>
</html>