Question of the Day for 21 Dec 2009

Question

Question of the Day for 21 Dec 2009

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
December 21, 2009 at 10:27 am

#74207

Discussions about the question: Riddle Me This[/url]

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I had to decipher the code based on the usual methodology. I haven't seen the Frosty the Snowman movie since the 70s. Amusing question though.

Steve: Is there a way to get the code in these to copy-and-paste without losing line-breaks? After I answer, I like to test it, and I have to go through and manually re-add line breaks after copying it into SSMS.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Thanks Gus. I was hoping for that kind of response.

Along the code copy comment - It would be nice to be able to use the ifCode for SQL code in the QOD. I think that may help with the line break issue. I didn't see that option being a QOD novice.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jedak SSCertifiable Points: 5577 More actions · Answer 3

GSquared (12/21/2009)
Steve: Is there a way to get the code in these to copy-and-paste without losing line-breaks? After I answer, I like to test it, and I have to go through and manually re-add line breaks after copying it into SSMS.

Agreed, I pulled the code out and put it in Query Analyzer to be able to read it better, and had to put all the line breaks. I enjoyed the question.

SanjayAttray SSChampion Points: 13157 More actions · Answer 4

Doesn't 8,1,16,16,25,27,2,9,18,20,8,4,1,25 stands numerical order for HAPPY BIRTHDAY in alphabets.

In code is selecting top 26.

SQL DBA.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

I am not certain I follow your question.

The code does do a select top 26, and then an additional insert after that for 27. The insert for 27 is an empty string.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 6

We have gone back and forth on the code and haven't had a good solution for people to enter it and then have it display as well as cut paste. I think if you drop it in a text editor and copy back out it works.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Thanks Steve. It only seems to happen every once in a while that the code doesn't copy so well from the QOD.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 8

BWAA-HAA!!! Frosty needs to get a set-based decoder ring! 😛

DECLARE @ToAllGoodPeople TABLE

(

RowNum INT,

Cypher VARCHAR(100)

)

INSERT INTO @ToAllGoodPeople

(RowNum, Cypher)

SELECT 1,'8.1.16.16.25.27.2.9.18.20.8.4.1.25' UNION ALL

SELECT 2,'1.14.4.27.7.15.15.4.23.9.12.12' UNION ALL

SELECT 3,'20.15.27.1.12.12'

SELECT REPLACE((SELECT CHAR(SUBSTRING('.'+p2.Cypher,N+1,CHARINDEX('.',p2.Cypher+'.',N)-N)+64)

FROM dbo.Tally t

CROSS JOIN @ToAllGoodPeople p2

WHERE p1.RowNum = p2.RowNum

AND t.N <= LEN(p2.Cypher)+1

AND SUBSTRING('.'+p2.Cypher,t.N,1) = '.'

ORDER BY t.N

FOR XML PATH(''))

,'[',' ')

FROM @ToAllGoodPeople p1

GROUP BY p1.RowNum

ORDER BY p1.RowNum

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vk-kirov SSCertifiable Points: 7686 More actions · Answer 9

Jeff Moden (12/21/2009)
... SELECT REPLACE((SELECT CHAR(SUBSTRING('.'+p2.Cypher,N+1,CHARINDEX('.',p2.Cypher+'.',N)-N)+64) FROM dbo.Tally t
...

Msg 208, Level 16, State 1, Line 13

Invalid object name 'dbo.Tally'. 😉

I guess there are natural numbers in this table. This would work:

...

SELECT REPLACE((SELECT CHAR(SUBSTRING('.'+p2.Cypher,N+1,CHARINDEX('.',p2.Cypher+'.',N)-N)+64)

FROM (SELECT number AS N FROM master.dbo.spt_values WHERE type = 'P') t

...

Toreador SSChampion Points: 11365 More actions · Answer 10

"Due to the Holiday Season, and Frosty being a part of the season, an easy question was in order. "

It might be easy if you're American. Who's Frosty the Snowman?!?

:unsure:

Jedak SSCertifiable Points: 5577 More actions · Answer 11

Toreador (12/22/2009)
"Due to the Holiday Season, and Frosty being a part of the season, an easy question was in order. "
It might be easy if you're American. Who's Frosty the Snowman?!?
:unsure:

Frosty the Snowman

Frosty the Snowman (TV Special)

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 12

vk-kirov (12/22/2009)
Msg 208, Level 16, State 1, Line 13
Invalid object name 'dbo.Tally'. 😉
I guess there are natural numbers in this table. This would work:

Heh... spend some more time on this forum and you won't have to make that guess (which was a good one, by the way).

Here's the article that explains how a Tally table works. Sorry for not posting it with the code...

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Jeff Moden (12/21/2009)
BWAA-HAA!!! Frosty needs to get a set-based decoder ring! 😛
DECLARE @ToAllGoodPeople TABLE
(
RowNum INT,
Cypher VARCHAR(100)
)
INSERT INTO @ToAllGoodPeople
(RowNum, Cypher)
SELECT 1,'8.1.16.16.25.27.2.9.18.20.8.4.1.25' UNION ALL
SELECT 2,'1.14.4.27.7.15.15.4.23.9.12.12' UNION ALL
SELECT 3,'20.15.27.1.12.12'
SELECT REPLACE((SELECT CHAR(SUBSTRING('.'+p2.Cypher,N+1,CHARINDEX('.',p2.Cypher+'.',N)-N)+64)
FROM dbo.Tally t
CROSS JOIN @ToAllGoodPeople p2
WHERE p1.RowNum = p2.RowNum
AND t.N <= LEN(p2.Cypher)+1
AND SUBSTRING('.'+p2.Cypher,t.N,1) = '.'
ORDER BY t.N
FOR XML PATH(''))
,'[',' ')
FROM @ToAllGoodPeople p1
GROUP BY p1.RowNum
ORDER BY p1.RowNum

I knew somebody would do this.

Thanks Jeff

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Thanks Jedak for the followup on the "Who is Frosty" question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events