How can I use T-SQL to generate a password from a table of words?
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.
-- create table CREATE TABLE [dbo].[tblWordList]( [pKey] [int] IDENTITY(1,1) NOT NULL, [word] [varchar](20) NULL, CONSTRAINT [PK_tblWordList] PRIMARY KEY CLUSTERED ( [pKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --insert some data INSERT INTO dbo.tblWordList (word) SELECT 'Ability' UNION SELECT 'Able' UNION SELECT 'Aboard' UNION SELECT 'About' UNION SELECT 'Above' UNION SELECT 'Accept' UNION SELECT 'Accident' UNION SELECT 'According' UNION SELECT 'Account' UNION SELECT 'Accurate' GO -- show inserted rows SELECT * FROM dbo.tblWordList GO
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.)
The complete T-SQL code is listed below.
select w1.word + w2.word + cast(dbo.randbetween(1,2000) as varchar(4)) + CHAR(dbo.randbetween(35,38)) as NewPassword from (select word from tblWordList where pKey= [dbo].[randbetween](1,2000)) w1, (select word from tblWordList where pKey= [dbo].[randbetween](1,2000)) w2
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.
- A More Versatile SQL Server Random Number Function
- Different ways to get random data for SQL Server data sampling
- Create Your Own RANDBETWEEN Function in T-SQL
- Generating Random Numbers in SQL Server Without Collisions
- SQL Server Random Sorted Result Set
- Our complete tutorial list
Last Update: 4/29/2015
About the author
View all my tips