Friday, October 13, 2006

SQL Teaser/Puzzle Inspired By Hugo Kornelis

Hugo Kornelis has posted an interesting article about using STUFF
The link to that article is here

He has a challenge in that article, the challenge is this:
Create this table

CREATE TABLE BadData (FullName varchar(20) NOT NULL);
INSERT INTO BadData (FullName)
SELECT 'Clinton, Bill' UNION ALL
SELECT 'Johnson, Lyndon, B.' UNION ALL
SELECT 'Bush, George, H.W.';

Split the names into 3 columns (not fields ;-) ), the shortest query wins

Your output should be this:
LastName FirstName MiddleInitial
Clinton Bill
Johnson Lyndon B.
Bush George H.W.


Hugo Kornelis posted a solution by using STUFF

SELECT FullName,LEFT(FullName, CHARINDEX(', ', FullName) - 1) AS LastName,
STUFF(LEFT(FullName, CHARINDEX(', ', FullName + ', ',CHARINDEX(', ', FullName) + 2) - 1), 1, CHARINDEX(', ', FullName) + 1, '') AS FirstName,
STUFF(FullName, 1,CHARINDEX(', ', FullName + ', ',
CHARINDEX(', ', FullName) + 2), '') AS MiddleInitial
FROM BadData

which according to editplus is 340 characters


I replied with this

SELECT FullName,PARSENAME(FullName2,NameLen+1) AS LastName,
PARSENAME(FullName2,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(FullName2,NameLen-1),'~','.'),'') AS MiddleInitial
FROM(
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName2,FullName
FROM BadData) x

my solution is 338 characters, so 2 characters less ;-)

Of course I could have 'cheated' a little by doing this

SELECT FullName,PARSENAME(F,NameLen+1) AS LastName,
PARSENAME(F,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(F,NameLen-1),'~','.'),'') AS MiddleInitial
FROM(
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS F,FullName
FROM BadData) x

And this is 306 characters

So let's see, who can do it in less characters

1 comment:

Anonymous said...

This is a little South of 300 characters. I think.

SQL Server would allow me to omit some of the ()'s and Alias references which would drop it down some.

SELECT FullName,LEFT(FullName,C)AS LastName,SUBSTRING(FullName,(C+2),(CASE WHEN D = 0 then C ELSE D-1 END))AS FirstName,CASE WHEN D=0 THEN ''ElSE SUBSTRING(FullName,(C+D+2),L)END AS MiddleInitial FROM (SELECT *,(CHARINDEX(',',FullName)-1) AS C,CHARINDEX(',',SUBSTRING(FullName,CHARINDEX(',',FullName)+1,LEN(FullName)))AS D,LEN(FullName)AS L FROM BadData)AS x




Thanks for the Puzzle!
jack