SQL Puzzle – Word Scrambler

2

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;

2 thoughts on “SQL Puzzle – Word Scrambler

  1. gserdijn says:

    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
    • 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,151 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: