SQL Puzzle – Word Scrambler

微信扫一扫,分享到朋友圈

SQL Puzzle – Word Scrambler

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.
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 <= 1="" 2="" len(my.word)="" where="" numbers.type="P" and="" numbers.number="" <="LEN(@Word)" charindex('="" ',="" '="" +="" @word,="" numbers.number)="Numbers.number" )="" --="" add="" a="" space="" before="" each="" word.="" the="" sort="" values="" are="" to="" keep="" words="" together="" spaces="" between="" them="" ,addspaces="" as="" (="" select="" sort1,="" sort2,="" word="" from="" stringsplit="" union="" all="" split="" up="" into="" it's="" letters="" ,wordsplit="" substring(my.word,="" numbers.number+1,="" 1)="" letter="" addspaces="" my="" join="" master..spt_values="" numbers="" on="" (datalength(my.word)="" 2)="" get="" rid="" of="" first="" stuff="" (select="" wordsplit="" order="" by="" newid()="" for="" xml="" path(''),type).value('.','varchar(max)'),1,1,'')="" word; Filed under: 
  DBA Humor , 
  Microsoft SQL Server , 
  SQLServerPedia Syndication , 
  T-SQL Tagged: 
  Humor , 
  Puzzle 
   
   
   
   
  
  
   
    
   
   
   

SQLStudies

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 .

微信扫一扫,分享到朋友圈

SQL Puzzle – Word Scrambler

Automating MySQL Backups with Gulp

上一篇

BLU Trying To Address The Android Update That Locks Its Phones

下一篇

你也可能喜欢

SQL Puzzle – Word Scrambler

长按储存图像,分享给朋友