Login     Register

        Contact Us     Search

Happy Holidays

Dec 14

Written by: Charles Flock
12/14/2009 8:12 PM  RssIcon

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')
 
You can get the full script for inserting all the letters here
 
5x5 Letters



7x7 Letters



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 from WestClinTech!

Happy Holidays!

Tags:
Categories:
Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service