Uncategorized

Grabbing a set and inserting ‘and’ before the last item in a set in SQL (with and without the oxford comma)

Our client wanted to display numbers that were drawn as follows:

“The numbers drawn were 01, 02, 03, 04 and 05, resulting in a win of $X.XX!”

Well, I had misinterpreted this, thinking he wanted all of the drawn numbers, but what he really wanted was just the numbers that cased the winnings to occur. The easy part was the INTERSECT statement to determine the numbers; the hard part was, because this was being inserted into an SSRS report, I needed to comma-delimit the numbers with an ‘and’ before the last item in the set. I made this snippet to get the logic right.

DECLARE @t TABLE (Num INT);
INSERT INTO @t VALUES (1);
INSERT INTO @t VALUES (2);
INSERT INTO @t VALUES (3);
INSERT INTO @t VALUES (2);
INSERT INTO @t VALUES (3);
INSERT INTO @t VALUES (2);
INSERT INTO @t VALUES (3);
INSERT INTO @t VALUES (2);
INSERT INTO @t VALUES (3);
DECLARE @oxfordComma BIT = 0;
WITH cte1 AS
(
SELECT STUFF ((SELECT ', ' + CAST(Num AS VARCHAR(MAX)) FROM @t FOR XML PATH ('')), 1, 2, '') AS X
)
--SELECT '|' + cte1.NumbersCommaDelimited + '|' FROM cte1
, cte2 AS
(
SELECT X, REVERSE(X) AS Rev FROM cte1
)
, cte3 AS
(
SELECT LEN(Rev) - CHARINDEX(',', Rev, 1) + CASE WHEN @oxfordComma = 1 THEN 2 ELSE 1 END AS Pos, X FROM cte2
)
, cte4 AS
(
SELECT STUFF(X, Pos, CASE WHEN @oxfordComma = 1 THEN 0 ELSE 1 END, ' and') AS Y, X, Pos FROM cte3
)
SELECT X, Pos, Y FROM cte4

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s