Happy Holidays
Dec
14
Written by:
Charles Flock
12/14/2009 8:12 PM
A look at the XLeratorDB JOINSTR function.
After working on a T-SQL challenge to produce a calendar that looked like it had been programmed in COBOL and printed on green bar paper in the 1980’s, I thought it might be interesting to explore some of the things that we can do using the XLeratorDB string functions. You can find out more about T-SQL Challenge 18 here.
Since the challenge prohibited the use of user-defined functions, it was harder than it would be in the real world. To show what you could do by using some XLeratorDB functions, I thought it might be fun to go all the way back to the future and try to create a holiday message that printed multiple characters to represent a single letter of the alphabet. The kind of thing that might look like this:
X X XXXXX XXXXX XXXXX X X X X XXXXX X XXXXX XXXX XXXXX X X XXXXX
X X X X X X X X X X X X X X X X X X X X X X X
XXXXX XXXXX XXXXX XXXXX X XXXXX X X X X X X XXXXX X XXXXX
X X X X X X X X X X X X X X X X X X X
X X X X X X X X X XXXXX XXXXX XXXXX XXXX X X X XXXXX
or like this:
▓ ▓ ▓▓▓▓▓ ▓▓▓▓▓ ▓▓▓▓▓ ▓ ▓ ▓ ▓ ▓▓▓▓▓ ▓ ▓▓▓▓▓ ▓▓▓▓ ▓▓▓▓▓ ▓ ▓ ▓▓▓▓▓
▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓
▓▓▓▓▓ ▓▓▓▓▓ ▓▓▓▓▓ ▓▓▓▓▓ ▓ ▓▓▓▓▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓▓▓▓▓ ▓ ▓▓▓▓▓
▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓
▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓ ▓▓▓▓▓ ▓▓▓▓▓ ▓▓▓▓▓ ▓▓▓▓ ▓ ▓ ▓ ▓▓▓▓▓
The only requirement that I had was that I would set up each ‘letter’ once and that the message to be printed would be input as text. This required that I design ‘letters’.
I decided that the letters needed to be symmetrical and laid them out in a 5 by 5 grid. For example, the letter ‘B’ looks like this:
XXXX
X X
XXXX
X X
XXXX
I also looked at a 7 by 7 grid:
XXXXXX
XX XX
XX X
XXXXX
XX X
XX XX
XXXXXX
Creating the letters was pretty easy, though ‘Q’ was a bit tricky. I put the results into a temporary table, which contained the letter, the line (or sequence) number of the grid, and the arrangement of X and space that I was using for that line (which I called print_line). For example, to represent the letter ‘A’ in the 7 by 7 format, I used:
CREATE TABLE #H (letter char(1), seq int, print_line nvarchar(max))
INSERT INTO #H VALUES ('A',0,'XXXXXXX')
INSERT INTO #H VALUES ('A',1,'XX XX')
INSERT INTO #H VALUES ('A',2,'XX XX')
INSERT INTO #H VALUES ('A',3,'XXXXXXX')
INSERT INTO #H VALUES ('A',4,'XX XX')
INSERT INTO #H VALUES ('A',5,'XX XX')
INSERT INTO #H VALUES ('A',6,'XX XX')
Creating the letters was the easy part. The bigger challenge was to get them to print with the proper orientation. When I wanted to review the letters, I selected all of them, and instead of getting A B C D horizontally, all were in a single column.
SELECT PRINT_LINE
FROM #H
WHERE letter in ('A', 'B', 'C', 'D')
Returns the following:
XXXXXXX
XX XX
XX XX
XXXXXXX
XX XX
XX XX
XX XX
XXXXXX
XX XX
XX X
XXXXX
XX X
XX XX
XXXXXX
XXXXXXX
XX
XX
XX
XX
XX
XXXXXXx
XXXXX
XX X
XX X
XX X
XX X
XX X
XXXXX
The JOINSTR and JOINSTR_q functions and an auxiliary number table solve this problem for me quite nicely. The JOINSTR function concatenates the text from multiple rows in a table. An auxiliary number table is simply a table containing sequence of integers from 1 and on. If you don’t have one, you can use this link to get an implementation designed by Itzik Ben-Gan and published in SQL Server magazine.
The JOINSTR_q function has three inputs. First is the separator, which I am going to set as two spaces. Next is the maximum number of items to include in this statement. Since I want everything, I am going to set that to NULL. The final parameter is the T-SQL statement, as a string, that I am using to select the rows that will be joined. Any valid T-SQL statement may be entered here, including the use of local variables, common table expressions (CTE), and derived tables. Since the entire statement must be passed as a string, any constants used in the T-SQL statement must be concatenated as a string.
In this example, we SELECT the letters from A to D.
select wct.JOINSTR_q(SPACE(2),null,'SELECT print_line
FROM #H H
WHERE letter BETWEEN ' + CHAR(39) + 'A' + CHAR(39) + 'AND ' + CHAR(39) + 'D' + CHAR(39) +'
AND h.seq = ' + cast(n.num-1 as nvarchar) + '
order by seq')
from numbers n
where n.num < 8
Returning the following result:
XXXXXXX XXXXXX XXXXXXX XXXXX
XX XX XX XX XX XX X
XX XX XX X XX XX X
XXXXXXX XXXXX XX XX X
XX XX XX X XX XX X
XX XX XX XX XX XX X
XX XX XXXXXX XXXXXXx XXXXX
Let’s look at the SQL string parameter. We are trying the select all the print lines from #H for the letters A through D. That SELECT statement is straightforward:
SELECT print_line
FROM #H H
WHERE letter BETWEEN 'A' and 'D'
Once we convert it to a string, we need some special manipulation to make sure that the ‘A’ and the ‘D’ are properly closed by single quote, ASCII character number 39. We have a function, QUOTES that simplifies that. Instead of writing this:
WHERE letter BETWEEN ' + CHAR(39) + 'A' + CHAR(39) + 'AND ' + CHAR(39) + 'D' + CHAR(39) +'
we write this:
WHERE letter BETWEEN ' + wct.QUOTES('A') + 'AND ' + wct. QUOTES('D') + '
Similarly, we need to cast the num column from the auxiliary numbers table, Numbers, as a varchar or nvarchar so that it is added as part of the string. By using the auxiliary number table, we are able to maintain the order of the print_line in JOINSTR_q function.
To genereate a HAPPY HOLIDAYS message, we would use the following SQL
SELECT WCT.JOINSTR_q(' ', null, 'DECLARE @T as nvarchar(max)
set @T = ' +wct.QUOTES('HAPPY HOLIDAYS') + '
SELECT
PRINT_LINE
FROM numbers n
,#H H
where n.num <= LEN(@T)
AND seq = ' + cast(n2.num-1 as nvarchar) + '
AND SUBSTRING(@T, N.NUM, 1) = H.LETTER
ORDER BY N.NUM, SEQ')
from numbers n2
where n2.num < 8
Which returns the following result
XX XX XXXXXXX XXXXXXX XXXXXXX X X XX XX XXXXXXX XX XXXXXXX XXXXX XXXXXXX X X XXXXXXX
XX XX XX XX XX X XX X X X XX XX XXXXXXX XX X XX X XX XX X X XX
XX XX XX XX XX X XX X X X XX XX XX XX XX X XX X XX XX X X XX
XXXXXXX XXXXXXX XXXXXXX XXXXXXX X XXXXXXX XX XX XX X XX X XXXXXXX X XXXXXXX
XX XX XX XX XX XX X XX XX XX XX XX X XX X XX XX X XX
XX XX XX XX XX XX X XX XX XXXXXXX XX X XX X XX XX X XX
XX XX XX XX XX XX X XX XX XXXXXXX XXXXXXX XXXXXXX XXXXX XX XX X XXXXXXX
If we wanted to generate a different message, we would simply change the @T variable declared in the SQL string in the JOINSTR_q function. Let’s say the message was PEACE ON EARTH, the SQL would like this:
SELECT WCT.JOINSTR_q(' ', null, 'DECLARE @T as nvarchar(max)
set @T = ' +wct.QUOTES('PEACE ON EARTH') + '
SELECT
PRINT_LINE
FROM numbers n
,#H H
where n.num <= LEN(@T)
AND seq = ' + cast(n2.num-1 as nvarchar) + '
AND SUBSTRING(@T, N.NUM, 1) = H.LETTER
ORDER BY N.NUM, SEQ')
from numbers n2
where n2.num < 8
Which returns
XXXXXXX XXXXXXX XXXXXXX XXXXXXX XXXXXXX XXXXXXX X X XXXXXXX XXXXXXX XXXXXXX XXXXXXX XX XX
XX X XX XX XX XX XX XXXXXXX XX X XX XX XX XX X X XX XX
XX X XX XX XX XX XX XX XX X X X XX XX XX XX X X XX XX
XXXXXXX XXXXXXX XXXXXXX XX XXXXXXX XX XX X X X XXXXXXX XXXXXXX XXXXXXX X XXXXXXX
XX XX XX XX XX XX XX XX X X X XX XX XX XX X X XX XX
XX XX XX XX XX XX XXXXXXX X XX XX XX XX XX X X XX XX
XX XXXXXXX XX XX XXXXXXx XXXXXXX XXXXXXX X X XXXXXXX XX XX XX X X XX XX
If we wanted to use the letter rather than ‘X’ in formatting we could write this:
SELECT WCT.JOINSTR_q(' ', null, 'DECLARE @T as nvarchar(max)
set @T = ' +wct.QUOTES('PEACE ON EARTH') + '
SELECT
REPLACE(PRINT_LINE, ' + wct.QUOTES('X') + ', letter)
FROM numbers n
,#H H
where n.num <= LEN(@T)
AND seq = ' + cast(n2.num-1 as nvarchar) + '
AND SUBSTRING(@T, N.NUM, 1) = H.LETTER
ORDER BY N.NUM, SEQ')
from numbers n2
where n2.num < 8
Which returns
PPPPPPP EEEEEEE AAAAAAA CCCCCCC EEEEEEE OOOOOOO N N EEEEEEE AAAAAAA RRRRRRR TTTTTTT HH HH
PP P EE AA AA CC EE OOOOOOO NN N EE AA AA RR R T HH HH
PP P EE AA AA CC EE OO OO N N N EE AA AA RR R T HH HH
PPPPPPP EEEEEEE AAAAAAA CC EEEEEEE OO OO N N N EEEEEEE AAAAAAA RRRRRRR T HHHHHHH
PP EE AA AA CC EE OO OO N N N EE AA AA RR R T HH HH
PP EE AA AA CC EE OOOOOOO N NN EE AA AA RR R T HH HH
PP EEEEEEE AA AA CCCCCCC EEEEEEE OOOOOOO N N EEEEEEE AA AA RR R T HH HH
Obviously, if we wanted to replace X with any character, we could just modify the REPLACE statement. Using the 5 by 5 grid, we will replace the X character with a star (NCHAR(9733)). Since this character is a double byte character, we also need to adjust single spaces to be double spaces. This is what the SQL looks like:
SELECT WCT.JOINSTR_q(' ', null, 'DECLARE @T as nvarchar(max)
set @T = ' +wct.QUOTES('PEACE ON EARTH') + '
SELECT
REPLACE(REPLACE(PRINT_LINE, ' + wct.QUOTES('X') + ', NCHAR(9733)), SPACE(1), SPACE(2))
FROM numbers n
,#H H
where n.num <= LEN(@T)
AND seq = ' + cast(n2.num-1 as nvarchar) + '
AND SUBSTRING(@T, N.NUM, 1) = H.LETTER
ORDER BY N.NUM, SEQ')
from numbers n2
where n2.num < 6
Which returns the following:
Happy Holidays!