Transform: Escape Templates

Escape Template

Suppose we would like to fill a text field with strings that will later
be used within SQL, say, to automatically build queries. We want
to include special characters in those strings, such as single '
quote characters that may have special meaning to SQL or within scripts,
but we want those special characters preserved and not acted upon. To
do that we must escape those
characters within the strings by prefixing them with a special character
that in later use will be interpreted as an escape
character.

The Escape template provides
a means of doing so that allows us to focus on the clarity of the end
result without getting confused by too much escaping of special characters.

Suppose in a table we want to fill the Temp_text
field, which is data type nvarchar,
with the string

abc \'def\' ghi

If we try to do that by using the Copy
template with a target field of Temp_text
and a source value of 'abc \'def\' ghi'
the result will be missing two backslash \
characters:

Because the backslash \ character
itself is a special character, used by SQL to indicate that the character
immediately following is to be taken literally, that is, escaped, we cannot
enter the backslash character by itself within a string in a combo box
that interprets its contents as SQL.

If we use a backslash \ character
in a string such as 'abc \'def\' ghi'
we are telling the system to take the single quote '
character immediately following the backslash \ character
as a literal. If we want to use the Copy
template to also insert backslash \ characters,
then in the source value we must escape those backslash characters as
well.

We can do that by using the Copy
template with a target field of Temp_text
and a source value of 'abc \\\'def\\\'
ghi' to create the result:

In the four character sequence \\\' the
first backslash character escaped the second backslash character and the
third backslash character escaped the single quote. When
parsed as an SQL expression the four character sequence \\\'
ends up going into the target field as the two character sequence \'.

That is exactly what we want but using so many escape characters is
difficult to read and risks confusion.

A simpler method is to use the Escape
template, which allows us to enter in the source, value string the string
we desire in simple form along with a string that enumerates just once
the character to be used as a special escape character, followed by a
list of all characters that are to be escaped by that character.

In the Escape box we put a string
where the first character is the
special character to be used as an escape character. All other
characters in the string after the first character are the characters
we would like to be escaped.

If we use Escape with a target
field of Temp_text and a source
value of 'abc \'def\' ghi' and
an Escape string of '\\\''
we right away get the result we want:

In the Escape string of '\\\'' (the last two characters
are two single quote ' characters)
we are telling the system to use a backslash \
character to escape any single quote ' characters
that occur within the source string before putting that source string
into the target field. Because, like all combo boxes in the Transform
dialog, the Escape combo
box is interpreted as an SQL expression, we have to escape both the backslash
character and the single quote character within the string. But
we only need to do that once and not repetitively as we would with Copy.

In the above examples Escape
is a convenience, as what we show using Escape
could also be done, albeit at greater risk of confusion, using the Copy template.

Where Escape shines is when
fields and not literals are used. In that situation the automatic
insertion of escape characters provided by Escape
goes well beyond what could be done with Copy.

Consider a case where we would like to utilize dates in the form of
12/14/2016 in some process where
the forward slash / character
would be treated as a special character if not escaped by being prefixed
with a backslash \ character.
Instead of dates in the form of 12/14/2016
we would like the string to be 12\/14\/2016,
and we would like to have that done automatically to whatever values we
take from a [Date] string. The
Escape template is perfect
for that.

If we use Escape with a target
field of Temp_text and a source
value of CAST ([Date] AS NVARCHAR)
and an Escape string of '\\/'

we get the result we want:

The source value uses a CAST
expression because the [Date]
field is a datetime type and must
be CAST into nvarchar
text type to be sent into the text Temp_text
target field.

In the Escape string the intended
first character is a backslash \
character, which itself within the string must be escaped. That
is the special character to use for escapes. The character
to be escaped is the slash / character
that follows. The result is that in the target field any slash
/ characters found in the
source string will be prefixed with the specified escape character, a
backslash \ character.

Although the backslash \ character
is most frequently used as an escape character in computing, using it
in examples here might be confusing because it itself must be escaped.
To avoid confusion let us create an example that uses
a different character as a special, escape character which does not have
meaning within SQL and thus does not need to be escaped.

In the following example we want to escape each occurrence of the zero
0 character with a special escape
character that will be the caret ^
character. In this example a date of the form 12/14/2016
would become 12/14/2^016.

We use Escape with a target
field of Temp_text and a source
value of CAST ([Date] AS NVARCHAR)
and an Escape string of '^0' to get the following result:

The Escape string in this
case is simple and easy to read because neither of the characters in it
themselves must be escaped. The first character, a caret ^ character, is the character
to use for our special, escape character. The second character,
the zero 0 character,
is the character that should be escaped wherever it occurs in the source
string.

We can extend this example to show how to specify more than one character
to be escaped. Suppose in our dates not only do we want any
zero 0 characters to be escaped
with a caret ^ character, we also
want any numeral one 1 characters
and any numeral four 4 characters
to also be escaped. We would like any date of the form 12/14/2016 to become ^12/^1^4/2^0^16.
That is easy to do.

We use Escape with a target
field of Temp_text and a source
value of CAST ([Date] AS NVARCHAR)
and an Escape string of '^014'

to get the desired result:

In the Escape string of '^014' the first character, a caret
^ character, is the character
to use for our special, escape character. The subsequent characters,
the zero 0 character, the numeral
one 1 character and the numeral
4 character, are the characters
that should be escaped wherever they occur in the source string.