Tame Those Strings Part 6 - Bending Results
This is a continuation of string manipulation techniques. If you are interested, you can read the other articles,
though you do not need to read it before this one. These are mostly beginning
programming articles, but advanced T-SQL programmers may still find something useful here.
- Part 1 deals with SUBSTRING and how it can be used to extract some information
from a field of data
- Part 2 deals with CHARINDEX and how it can be used to extract some information
from a field of data when the data is delimited rather than stored in a particular format.
- Part 3 deals with REPLACE and how it can be used to remove unwanted information
from a field of data when the data is not in a known format.
- Part 4 deals with numeric conversions.
- Part 5 deals with STUFF.
Introduction
I discovered an interesting string technique the other day while reading
a SQL programming book. It presented a great way that one can build a
string with a delimited list of items. It can also be used to "bend" a
result set from a series of rows to a column. I am sure many of you can
come up with a whole bunch of ideas that I would never think of, but this actually
solved a problem that I had previously used a cursor to solve. And eliminating cursors is
almost always a good thing.
The Problem
I have a number of email integration processes that send notifications to
people. Most of these are business processes and the names of the people who
recieve the emails can change at any time. We store these emails in a table called,
appropriately, email_notification. This table is defined with the following DDL:
create table email_notification
( id int identity( 1, 1),
process varchar( 80),
recipients varchar( 255),
cc varchar( 255)
)
and contains data like the following:
id process recipients cc ---- -------------------- ----------------------------------------------------- ------ 1 Business Process 1steve@dkranch.net;steve@sqlservercentral.com 2 Business Process 2delaney@dkranch.net 3 Business Process 3kyle@dkranch.net;kendall@dkranch.net 4 Business Process 4tia@dkranch.net
This has worked fine as in various stored procedures as they can select a field from
this table and use it in the xp_sendmail stored procedure. Emails go out fine
and to multiple recipients. The problems come if I want to edit this table. If I want
to add or remove an email from the list, I have to perform some string manipulation to ensure
that I edit everything correctly.
Let's say that I want to remove Delaney from all the lines above. I have to first find all
instances of delaney@dkranch.net and then replace them with spaces. Not the REPLACE function
(see Part 3 of this series) will
perform this work, but I have two cases here. I have to allow for the email at the end of the
email separately from being in the middle. The reason: the semicolons that separate the emails.
This is not a huge problem and the REPLACE function will handle most edits that are needed, the
other problem is that this storage is fundamentally a poor design. I am using a single field to hold
multiple data elements. If I wanted to search on multiple emails, I have problems. If I want to
total the number of emails a series of people recieve, I cannot easily do this.
Using this storage solution, however, has allowed me to easily send a group of emails into the
mail stored procedure without using a cursor to concatenate the data. I can get a list of emails
using the following code:
declare @r varchar( 255) select @r = recipients from email_notification where process='Business Process 1'
However, what I really want to get to is to have the data stored like this:
id process recipients cc ---- -------------------- ----------------------------------------------------- ------ 1 Business Process 1steve@dkranch.net 2 Business Process 1steve@sqlservercentral.com 3 Business Process 2delaney@dkranch.net 4 Business Process 3kyle@dkranch.net 5 Business Process 3kendall@dkranch.net 6 Business Process 4tia@dkranch.net
How can I do it? Read on...
The Solution
I have to admit that I would probably never have stumbled on this technique if I had read about
it. That's a good reason to keep reading (hint, hint).
I have known for some time that there is a SET operator in T-SQL that can be used to set the value
of a variable. I have never bothered to use it, preferring the SELECT operator. I don't have a good reason,
I just like the SELECT operator. I had also assumed that these two operators performed the same
function and one was kept for backwards compatibility.
Then I read about what the differences were in an advanced T-SQL programming book. The SET statement
can only assign a single value, where SELECT can assing multiple values.
OK, that is interesting, but how does that help me. I don't really want to assign two variables
like this:
declare @r varchar( 255), @c varchar( 255) select @r = recipients, @c = cc from email_notification where process='Business Process 1'
Or even this:
declare @r varchar( 255), @c varchar( 255) select @r = substring( recipients, 1, charindex( ';', recipients)-1), @c = substring( recipients, charindex( ';', recipients)+1, 255) from email_notification where process='Business Process 1' select @r, @c
It was then that I kept reading and learned about another feature of the SELECT statemet.
I have known that I can assign a single value to a variable even if the query returns mutliple
results. For example, look at his code:
declare @r varchar( 255) select @r = recipients from email_notification
What will be the value of @r? It will be the last value returned by the result set. In this case
the value will be "tia@dkranch.net". So does this help me?
Well, T-SQL is incredibly flexible and able to embed it's commands within other commands, much
like I used to do in LISP early in my career. The SELECT statement will allow me to add an expression
to the query inside the assinment and allow T-SQL to perform my concatenation.
Assume my data set now looks like this:
id process recipients cc ---- -------------------- ----------------------------------------------------- ------ 1 Business Process 1steve@dkranch.net 2 Business Process 1steve@sqlservercentral.com 3 Business Process 2delaney@dkranch.net 4 Business Process 3kyle@dkranch.net 5 Business Process 3kendall@dkranch.net 6 Business Process 4tia@dkranch.net
I can run the following:
declare @r varchar( 1000) select @r = '' select @r = rtrim(@r) + recipients + ';' from email_notification where process = 'business process 1' select substring( @r, 1, len( @r) - 1)
Note that I initialize my variable to a blank string and then allow T-SQL to concatenate my
string for me. The result is "steve@dkranch.net;steve@sqlservercentral.com". The last substring
is designed to merely remove the final semicolon from the string.
I included the sample script that I used to write this article here:tam