SQL Puzzle – Word Scrambler

November 29, 2017 by Kenneth Fisher

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;

Category: DBA Humor, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: Humor, Puzzle

2 thoughts on “SQL Puzzle – Word Scrambler”

gserdijn says:

November 29, 2017 at 3:28 PM

Well, it’s a function. Definitely not better and I’m quite positive that all formatting will be lost. So the code also will be scrambled, which fits in a way.
Anyway, it worked on my PC…

CREATE FUNCTION dbo.Scramble (@Word NVARCHAR(2048))
RETURNS NVARCHAR(2048)
AS

BEGIN 

DECLARE @W NVARCHAR(2048);

WITH Letters AS /* number each letter and mark the spaces */
(   SELECT
        numbers.number,
        SUBSTRING(my.word, numbers.number, 1) AS letter,
        CASE
            WHEN SUBSTRING(my.word, numbers.number, 1) = ' ' THEN 1
            ELSE 0
        END AS wordseparator 
    FROM (  SELECT @Word word) AS my
                JOIN master..spt_values numbers ON numbers.number <= LEN(my.word)
            WHERE numbers.type = 'P'
                AND numbers.number <= LEN(@Word)
),
Splitted AS /* sum the space markers from all previous rows to group the letters into words */
(  SELECT
        *,
        (   SELECT
                SUM(wordseparator)
            FROM Letters x
            WHERE x.number < Letters.number) AS wordnr, 
            ABS(CHECKSUM(PWDENCRYPT(letter))) / 2147483647.0 AS randomizer 
            /* Not 2B used in production code: https://ssdba.wordpress.com/2014/01/13/using-or-simulating-rand-in-a-t-sql-user-defined-function/ */
    FROM Letters
)
SELECT @W = (SELECT
            letter
        FROM Splitted
        ORDER BY 
            wordnr, 
            wordseparator, /* space first */
            randomizer /* and the rest of it  */
        FOR XML PATH (''), TYPE).value('.', 'VARCHAR(MAX)')
        /* don't interpret values as XML */

RETURN @W;
END;
GO

SELECT dbo.Scramble ('Scrambled eggs as usual ');
GO

Kenneth Fisher says:

November 30, 2017 at 8:39 AM

I haven’t had a chance to look at it in detail (although props on the function) but I fixed the formatting. In WP you can use a [ code ] [ / code ] block to get it to format correctly.

Reply