Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 71 - Play mangled words game in TSQL

Many of you must have played the 'mangled words' game in the school. Here is a challenge that gives you an opportunity to play it once again using TSQL.

Your task is to process the input table that contains several mangled words and try to 'un-mangle' them and validate them against a 'dictionary' table. You might be able to create more than one correct word from some of the input strings. It is also possible that a mangled word is completely incorrect and no valid word can be created from it.

As an example, given "xtet", the valid word that can be formed is "text".

Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Sample Data

The table MangledWords contains the randomize word list.

ID MangledWord
-- ------------------
1  lnlaheecg neplheta
2  etxt vrsere 

The table Dictionary contains the valid words.

ID Word
-- ---------
1  challenge
2  enigma
3  angle
4  change
5  changing
6  server
7  ant
8  elephant

Expected Results

ID CorrectWords
-- ---------------------------------------
1  angle, ant, challenge, change, elephant
2  server

Rules

  1. The output should be ordered by ID.
  2. The valid words(if found) will be sorted in Ascending order in the CorrectWords column.
  3. If no words can be formed, then the CorrectWords column should contain "No matching word found".
  4. The word in MangledWords table are separated by 1 space.
  5. A mangled word can contain a word which has fewer letters.
  6. You can assume the database to have a case-insensitive collation.
  7. If a word occurs in more than one mangled word it should be shown only once in a row.
  8. The maximum length of a word is limited to 30 characters.
  9. The word will contain only the 26 letters from a to z.

Sample Script

Use the TSQL Script given below to generate the source table and fill them with sample data.

  
IF OBJECT_ID('TC71_MangledWords','U') IS NOT NULL BEGIN   
	DROP TABLE TC71_MangledWords  
END  
GO    
CREATE TABLE TC71_MangledWords(ID INT IDENTITY, MangledWords VARCHAR(4000))  
GO    
INSERT INTO TC71_MangledWords(MangledWords)  
SELECT 'lnlaheecg neplheta' UNION ALL
SELECT 'etxt vrsere'

SELECT * FROM TC71_MangledWords 
GO
IF OBJECT_ID('TC71_Dictionary','U') IS NOT NULL BEGIN   
	DROP TABLE TC71_Dictionary  
END  
GO    
CREATE TABLE TC71_Dictionary(ID INT IDENTITY, Word VARCHAR(4000))  
GO    
INSERT INTO TC71_Dictionary(Word)  
SELECT 'challenge' UNION ALL
SELECT 'enigma' UNION ALL
SELECT 'angle' UNION ALL
SELECT 'change' UNION ALL
SELECT 'changing' UNION ALL
SELECT 'server' UNION ALL
SELECT 'ant' UNION ALL
SELECT 'elephant'

SELECT * FROM TC71_Dictionary
    

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Niladri Biswas
7 · 21% · 6720
Submit a Solution
Previous Challenge
Next Challenge
11
Liked
58
Comments
75
Solutions



Submit

TSQL Challenge 71 - Play mangled words game in TSQL

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.