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.
AS 
-- 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
	UNION ALL
	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 STUFF (
	(SELECT Letter FROM WordSplit
	ORDER BY Sort1, Sort2, NEWID()
	FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS Word;