1. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579

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-17-08 at 23:24.

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Ha Pat, that's clever. I can't think of a single reason why you'd need this for a real problem, but hey!

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
where is the puzzle part of the puzzle? i mean, what are we supposed to try to figure out?

4. King of Understatement
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Sorry. I don't get it either. MSQLbation perhaps?

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

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. SQL Consultant
Join Date
Apr 2002
Location
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

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595