SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Puzzle – Word Scrambler

I’ve been writing crosswords for the last few months and to change things up I thought I would do a word scramble. Well, step one was to create a function that would scramble the words for me. Below is my attempt at creating a word scrambler. Your task (should you choose to accept it) is to create a better one. (This laptop/computer will self-destruct in 30 seconds.)

For the most part, I commented most of it pretty well (I think). I do want to point out it’s a procedure, not a function because you can’t use NEWID() in a function call and I’m using that to randomize the order. If you’ve got a way to make that work then I can’t wait to see it. (I’ve seen the create a view with newid() then a function on top of that. I didn’t want to go that route.)

-- This procedure takes a string (up to 2048 characters) and scrambles each word.
-- Words are defined by spaces.
-- Words are kept in the initial order.
-- Punctuation will be scrambled with the word attached to it.

CREATE PROCEDURE dbo.WordScramble (@Word nvarchar(2048))
-- Max is 2048 characters because that is the maximum number of 
-- values in spt_values for type = 'P'.  If you are using a 
-- numbers table you can increase that value based on the number
-- of values available.
-- Split the string up into individual words (if there is more than one)
WITH StringSplit AS (
	SELECT number as Sort1, 
		SUBSTRING(@Word, Numbers.number, CHARINDEX(' ', @Word + ' ', Numbers.number) - Numbers.number) AS Word
	FROM (SELECT @Word Word) My
	JOIN master..spt_values Numbers 
		ON Numbers.number <= len(My.Word)
	WHERE Numbers.type = 'P'
	  AND Numbers.number <= LEN(@Word)
	  AND CHARINDEX(' ', ' ' + @Word, Numbers.number) = Numbers.number
-- Add a space before each word. The sort values are to keep
-- the words together and the spaces between them
,AddSpaces AS (
	SELECT Sort1, 1 as Sort2, ' ' AS Word FROM StringSplit
	SELECT Sort1, 2 as Sort2, Word FROM StringSplit )
-- Split each word up into it's letters
,WordSplit AS (
	SELECT Sort1, Sort2,  
		SUBSTRING(My.Word, Numbers.number+1, 1) AS Letter
	FROM AddSpaces My
	JOIN master..spt_values Numbers 
		ON Numbers.number < (datalength(My.Word)/2)
	WHERE Numbers.type = 'P'
-- Get rid of the first space
	(SELECT Letter FROM WordSplit
	ORDER BY Sort1, Sort2, NEWID()
	FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS Word;

Filed under: DBA Humor, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: Humor, Puzzle


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...