Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Unanswered: Transact SQL puzzle

    Here's a fun little puzzle that I thought up based on some rather gnarly problems I faced today. It might brighten up your morning. See if you can figure out what it does and how it does it.

    How does it work? When will it fail as written (this example does not fail)? Why will it fail? How can you change it to increae its capacity?
    Code:
    --  ptp  20080117  SQL puzzle
    
    DECLARE @i		INT
    ,  @c			VARCHAR(360)
    
    SET @i = 90
    SET @c = ''
    
    WHILE 64 < @i
       BEGIN
          SET @c = Char(@i) + Space(@i - 64) + @c
          SET @i = @i - 1
       END
    
    SELECT Replace(Replace(Replace(Replace(@c
    ,  '     ', ' '), '    ', ' '), '   ', ' '), '  ', ' ')
    UNION SELECT @c
    -PatP
    Last edited by Pat Phelan; 01-18-08 at 00:24.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ha Pat, that's clever. I can't think of a single reason why you'd need this for a real problem, but hey!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where is the puzzle part of the puzzle? i mean, what are we supposed to try to figure out?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I dunno either. Perhaps running through the answers will help figure out what the puzzle is....

    It starts with the ASCII value for Z and runs through a loop, decrementing down through the alphabet to ASCII value for A. These values plus by a number of spaces equal to number the character is in the alphabet (26, 25, 24 etc) are prepended (opposite of appended????) to a string.

    It will fail if the varchar length drops (trunaction), the loop extends beyond 64 without the varchar length increasing to match (truncation).

    .....
    Nope - don't get it.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry. I don't get it either. MSQLbation perhaps?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by blindman
    MSQLbation perhaps?
    can't be. it's not using a sex-based method.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Drat, I found this one quite interesting in its own way. It was a learning experience for one of the folks that I work with now. I'll try one more shot to see if it piques any interest, and will explain whatever is left tonight or tomorrow.
    Code:
    --  ptp  20080117  SQL puzzle
    
    DECLARE @i		INT
    ,  @c			VARCHAR(999)
    
    SET @i = 122
    SET @c = ''
    
    WHILE 64 < @i
       BEGIN
          SET @c = Char(@i) + Space(@i - 64) + @c
          SET @i = @i - 1
       END
    
    SELECT Replace(Replace(Replace(Replace(@c
    ,  '     ', ' '), '    ', ' '), '   ', ' '), '  ', ' ')
    UNION SELECT @c
    Now the example "breaks" (the spacing isn't consistant in the final result). Where? How? Why?

    What do the nested calls to Replace() simulate (it ain't iteration).

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, duh, it breaks at 1000 because you set @c to be only VARCHAR(999)

    i still fail to see why this is in the least bit interesting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'll explain the things about this that intrigued me. Thre people tried, and one person completely worked through the process (via PMs), so I don't feel like I blew it completely, but I didn't manage to raise the interest that I hoped for with this puzzle.

    The WHILE loop seems to have thrown a few folks... It was simply the shortest way I knew to generate strings of arbitrary size that were made up of varying length runs of spaces separated by "markers" that were visually distinct non-space characters so that everyone could easily see and identify what was happening.

    What this code does is a special case of "noise reduction" that converts runs of spaces into a single space. The idea is that data "noise" takes many forms, but it makes things hard to process. Noise reduction takes input that is unusable in its current form, and reduces it to a form that is usable.

    The nested function calls actually simulate recursion. Using functions this way gets past the issues with SQL procedure/function stack depth. Each of the function calls processes an input string one character shorter than the previous call, which simulates how recursive function calls could be used to solve this kind of problem.

    If you think of the run of spaces that the replace function searches for as the "original pattern", its length determines how long a run of spaces in the input string can be processed. The actual computation is complex (especially when the patterns get complex and can allow overlap), but for a starting pattern of length N you can always safely process runs of at least N * N - 1 patterns.

    The original problem was a complex one involving a manufacturing process. The proposed solution involved CLR routines, obscure code, and the potential problems with ongoing maintenance made me nervous. A few lines of Transact-SQL replaced the relatively hairy stuff with something that can be easily scripted and means a vastly less complex (and I'll bet faster overall) solution to the problem.

    Sorry that I couldn't make this puzzle more intriguing. I'll give it another shot when I run across another fun/interesting opportunity.

    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    well, duh, it breaks at 1000 because you set @c to be only VARCHAR(999)
    You have a good point. 999 was more than enough to show what I thought was interesting so I cut it off there hoping that would lead people to think about what was there and how that worked, but I ought to have allowed enough space to contain the whole generated string.

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, ok. But next time explain the requirements of the problem BEFORE giving the solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •