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).
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.
-- ptp 20080117 SQL puzzle
DECLARE @i INT
, @c VARCHAR(999)
SET @i = 122
SET @c = ''
WHILE 64 < @i
SET @c = Char(@i) + Space(@i - 64) + @c
SET @i = @i - 1
, ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' ')
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).
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.
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.