Generating a Password in T-SQL from a Table of Words

Problem

How can I use T-SQL to generate a password from a table of words?

Solution

In this tip, we will demonstrate how to write a T-SQL query that will create
a password containing two words from a table of words, a number and a symbol. We
will be utilizing the RANDBETWEEN function described in the tip
Create Your Own RANDBETWEEN Function in T-SQL. Please review this tip first
and create the function first.

We are starting with a table with a primary key column and a word column as shown
below. There are 2,000 words in the table and they are sorted alphabetically with
the first character in upper case.

In the subquery in line 9 shown below, we are using the RANDBETWEEN function to generate
a number between 1 and 2000 that is used in the WHERE clause to pick one word from
the table. This is the first word in the password. We repeat the subquery code in
line 10 to select the second word in the password. The two random words are concatenated
in lines 4 and 5 of the query. In line 6 we concatenate a random number between
1 and 2000. In line 7, we use the RANDBETWEEN function to generate a random number
between 35 and 38. These are the decimal ASCII codes for the hashtag (#) dollar
sign ($), percent sign (%), and ampersand (&), respectively. We use the CHAR function
to return the ASCII character represented by the decimal value returned by the RANDBETWEEN
function. This produces the password shown at bottom of the figure below.

In the word list used for this tip, the minimum word length is 4 so we are always
guaranteed a password with a minimum length of 11 (4 characters for the first word,
4 characters for the second word, 1 for the number and 1 for the symbol.)

Next Steps

Make sure that your word table does not contain words that when combined with
other words may be derogatory or offensive. You can easily adjust your word table
and the T-SQL code above to meet the password requirements of your organization.
Also, please check out these other tips and tutorials on T-SQL and the RAND() function
on MSSQLTips.com.