Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Question of the Day for 21 Dec 2009 Expand / Collapse
Author
Message
Posted Monday, December 21, 2009 10:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:02 PM
Points: 33,153, Visits: 15,284
Discussions about the question: Riddle Me This






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #837369
Posted Monday, December 21, 2009 12:27 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #837458
Posted Monday, December 21, 2009 12:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 21,617, Visits: 15,271
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #837471
Posted Monday, December 21, 2009 1:32 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 1,863, Visits: 1,000
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.
Post #837537
Posted Monday, December 21, 2009 1:45 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #837546
Posted Monday, December 21, 2009 3:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 21,617, Visits: 15,271
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #837598
Posted Monday, December 21, 2009 4:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:02 PM
Points: 33,153, Visits: 15,284
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #837641
Posted Monday, December 21, 2009 4:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 21,617, Visits: 15,271
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #837651
Posted Monday, December 21, 2009 7:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #837689
Posted Tuesday, December 22, 2009 12:32 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
...

Post #837760
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse