Question of the Day for 21 Dec 2009

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

  • 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

  • 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

  • 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.

  • 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.

  • 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

  • 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.

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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

    ...

  • "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:

  • 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)

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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

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

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