July 30, 2008 at 7:33 am
Is possible with SQL create crossword puzzle named word square from table with words?
Word square is a set of words such that when arranged one beneath another in the form of a square they read alike horizontally and vertically.[/size]
Examples:
HEART SATOR
EMBER AREPO
ABUSE TENET
RESIN OPERA
TREND ROTAS
March 27, 2015 at 8:46 am
This comes up while Googling so I will resurrect it from the grave.
This solution is for 4x4 wordsquares, not five letter. The performance will be worse with 5x5, as there are more 5 letter words, and more things to compute. But still doable, I'm sure.
First, let's create the table. It will have about 4,000 rows and 4 columns:
CREATE TABLE [dbo].[allwords](
[A] [char](1) NOT NULL,
[char](1) NOT NULL,
[C] [char](1) NOT NULL,
[D] [char](1) NOT NULL)
For performance, let's create the indexes now. There are a boatload of comparisons that happen, so we have to speed it up if we want results today.
CREATE INDEX IXABCD ON allwords ( A,B,C,D)
CREATE INDEX IXABDC ON allwords ( A,B,D,C)
CREATE INDEX IXACBD ON allwords ( A,C,B,D)
CREATE INDEX IXACDB ON allwords ( A,C,D,B)
CREATE INDEX IXADBC ON allwords ( A,D,B,C)
CREATE INDEX IXADCB ON allwords ( A,D,C,B)
CREATE INDEX IXBCDA ON allwords ( B,C,D,A)
CREATE INDEX IXBCAD ON allwords ( B,C,A,D)
CREATE INDEX IXBDCA ON allwords ( B,D,C,A)
CREATE INDEX IXBDAC ON allwords ( B,D,A,C)
CREATE INDEX IXBACD ON allwords ( B,A,C,D)
CREATE INDEX IXBADC ON allwords ( B,A,D,C)
CREATE INDEX IXCDAB ON allwords ( C,D,A,B)
CREATE INDEX IXCDBA ON allwords ( C,D,B,A)
CREATE INDEX IXCADB ON allwords ( C,A,D,B)
CREATE INDEX IXCABD ON allwords ( C,A,B,D)
CREATE INDEX IXCBAD ON allwords ( C,B,A,D)
CREATE INDEX IXCBDA ON allwords ( C,B,D,A)
CREATE INDEX IXDABC ON allwords ( D,A,B,C)
CREATE INDEX IXDACB ON allwords ( D,A,C,B)
CREATE INDEX IXDCAB ON allwords ( D,C,A,B)
CREATE INDEX IXDCBA ON allwords ( D,C,B,A)
CREATE INDEX IXDBCA ON allwords ( D,B,C,A)
CREATE INDEX IXDBAC ON allwords ( D,B,A,C)
Populate the table:
INSERT INTO dbo.allwords
(
dbo.allwords.A,
dbo.allwords.B,
dbo.allwords.C,
dbo.allwords.D
)
VALUES
--your list of words here in the format of ('a', 'b', 'c', 'd'),
Here is a list of scrabble words to use:
http://www.wordfind.com/4-letter-words/
And here is the notepad++ find/replace regexes to put this list in the correct format. Make sure your search is in Regular expression mode.
Add linebreaks (they go away when pasting for me)
Find: (.)(.)(.)(.)
Replace: $1$2$3$4\r
Format as SQL insert statement
Find: ^(.)(.)(.)(.)$
Replace: \('\1','\2','\3','\4'\),
Drop the very last comma.
Now for the actual select statement:
Get all word squares:
SELECT
CONCAT(row1.A,' ',row1.B,' ',row1.C,' ',row1.D) + CHAR(13)+CHAR(10) +
CONCAT(row2.A,' ',row2.B,' ',row2.C,' ',row2.D) + CHAR(13)+CHAR(10) +
CONCAT(row3.A,' ',row3.B,' ',row3.C,' ',row3.D) + CHAR(13)+CHAR(10) +
CONCAT(row4.A,' ',row4.B,' ',row4.C,' ',row4.D) + CHAR(13)+CHAR(10)
FROM
dbo.allwords row1,
dbo.allwords row2,
dbo.allwords row3,
dbo.allwords row4,
dbo.allwords col1,
dbo.allwords col2,
dbo.allwords col3,
dbo.allwords col4
WHERE
(row1.A = col1.A AND row2.A = col1.B AND row3.A = col1.C AND row4.A = col1.D)
AND (row1.B = col2.A AND row2.B = col2.B AND row3.B = col2.C AND row4.B = col2.D)
AND (row1.C = col3.A AND row2.C = col3.B AND row3.C = col3.C AND row4.C = col3.D)
AND (row1.D = col4.A AND row2.D = col4.B AND row3.D = col4.C AND row4.D = col4.D)
If you want to get all word squares that contain 8 unique words, add this to the end:
AND (
(row1.A != col1.A OR row1.B != col1.B OR row1.C != col1.C OR row1.D != col1.D)
AND (row2.A != col2.A OR row2.B != col2.B OR row2.C != col2.C OR row2.D != col2.D)
AND (row3.A != col3.A OR row3.B != col3.B OR row3.C != col3.C OR row3.D != col3.D)
AND (row4.A != col4.A OR row4.B != col4.B OR row4.C != col4.C OR row4.D != col4.D)
)
This will run for a very long time, but you should start seeing results immediately.
I recommend not actually using the scrabble words if you want any good word squares out of it. This is some of the strange results you get while doing so:
s o b a
o l l a
f e a l
a a h s
s o b a
o l l a
l e a l
a a h s
s o b a
o l l a
m e a l
a a h s
s o b a
o l l a
r e a l
a a h s
s o y a
i d e a
m e a l
a a h s
s o y a
i l e a
m e a l
a a h s
s o b a
o l l a
d e a l
a a h s
m a y a
u r e a
r e a l
a a h s
March 31, 2015 at 7:08 pm
standardbluecaboose (3/27/2015)
This comes up while Googling so I will resurrect it from the grave.
Hope that's not a pun with Easter being nearly upon us.
standardbluecaboose (3/27/2015)
s o b a
o l l a
f e a l
a a h s
s o b a
o l l a
l e a l
a a h s
s o b a
o l l a
m e a l
a a h s
s o b a
o l l a
r e a l
a a h s
s o y a
i d e a
m e a l
a a h s
s o y a
i l e a
m e a l
a a h s
s o b a
o l l a
d e a l
a a h s
m a y a
u r e a
r e a l
a a h s
Unfortunately those don't read the same vertically as horizontally, as in the OP's example.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 31, 2015 at 7:40 pm
This will produce a 5x5 square of 5, 5-letter words that read the same across as down.
WITH UNIQUEnTuples (n, Tuples) AS
(
SELECT 1, CAST(strcol AS VARCHAR(max))
FROM @t
UNION ALL
SELECT 1 + n.n, t.strcol + ',' + n.Tuples
FROM @t t JOIN UNIQUEnTuples n ON t.strcol < n.Tuples
WHERE CHARINDEX(t.strcol, n.Tuples) = 0 AND n < 2
),
MatrixOfLetters AS
(
SELECT rn, cn, RandomLetter=MAX(rl)
FROM UNIQUEnTuples a
CROSS APPLY
(
SELECT Tuples=CASE n WHEN 1 THEN Tuples + ',' + Tuples ELSE Tuples END
,RandomLetter=CHAR(65+ABS(CHECKSUM(NEWID()))%26)
) b
CROSS APPLY
(
-- VALUES (CAST(LEFT(b.Tuples, 1) AS INT), CAST(RIGHT(b.Tuples, 1) AS INT), RandomLetter)
-- ,(CAST(RIGHT(b.Tuples, 1) AS INT), CAST(LEFT(b.Tuples, 1) AS INT), RandomLetter)
SELECT CAST(LEFT(b.Tuples, 1) AS INT), CAST(RIGHT(b.Tuples, 1) AS INT), RandomLetter
UNION ALL SELECT CAST(RIGHT(b.Tuples, 1) AS INT), CAST(LEFT(b.Tuples, 1) AS INT), RandomLetter
) c (rn, cn, rl)
GROUP BY rn, cn
)
SELECT rn, cn, RandomLetter
INTO #Temp
FROM MatrixOfLetters;
SELECT word=
(
SELECT RandomLetter + ''
FROM #Temp b
WHERE a.rn = b.rn
ORDER BY cn
FOR XML PATH('')
)
FROM #Temp a
GROUP BY rn
ORDER BY rn;
GO
DROP TABLE #Temp;
It is not guaranteed to produce words but you could run the result against a dictionary of 5 letter words and keep any that match 5x5.
Explanation? What you want an explanation too? Jeez. OK.
Start here: Generating n-Tuples with SQL[/url]
The CROSS APPLY VALUES thing is here: An Alternative (Better?) Method to UNPIVOT (SQL Spackle) [/url]
Then for the concatenation, read this: Creating a comma-separated list (SQL Spackle) [/url]
If you still have questions let me know.
That was fun! 😛 Now on to serious business.
Edit: Ooops! Forgot CROSS APPLY VALUES is not available in SQL 2005, so I modified it to use SELECT/UNION ALL/SELECT but the concept is the same.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply