Question of the Day for 21 Dec 2009

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714637

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

  • GSquared

    SSC Guru

    Points: 260824

    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: 281210

    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

    SSCarpal Tunnel

    Points: 4864

    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

    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: 281210

    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: 714637

    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: 281210

    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: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • vk-kirov

    SSCertifiable

    Points: 7686

    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: 11222

    "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

    SSCarpal Tunnel

    Points: 4864

    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: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • SQLRNNR

    SSC Guru

    Points: 281210

    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: 281210

    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