The Transform dialog provides a variety of Transform templates that facilitate working with escape characters in different situations.
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 we want to fill the Temp_text field 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.