SQLServerCentral Article

Tame Those Strings - Part 6

,

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

Rate

5 (4)

Share

Share

Rate

5 (4)