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;

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