When composing a formula while creating a Computed Column, surrounding a value with double quotation marks (") on either side designates it as a text value—also called a string. For example, "3" allows something like the number 3 to be handled as a character rather than as a numeric value for use in a text / string function such as FIND.
The power of the double-quote is such that even surrounding characters that normally indicate operations (e.g., <, >, +, *, etc.) renders them into inert pieces of harmless text. At some point in writing formulas, the question becomes: What if I want to turn a double-quote character into text?
Using the Backslash
Nullifying the power of the double-quote in a formula so that " becomes just ordinary text, requires the use of what is called an “escape character.” Different types of software have different rules for what constitutes an escape character, but Paxata adheres to the standard set by the Java™ programming language. It uses the \ (backslash) as an escape character and so does Paxata.
An escape character operates by telling the application that a special character which normally requires the computer to do something is about to follow and—in this instance—the computer should ignore how it usually sees this single character and instead treat it like ordinary text. Thus, when we put a backslash in front of a double-quotation mark (like this: \") the computer knows that rather than expecting the double-quote to mark the boundaries of text, it should treat the " as if it were just a string value.
Using FIND as an example, there might be a case where you need to determine if a double-quotation mark exists in a dataset column. When combined with the IF function, a Computed Column could be created that would indicate that there was a double-quote character within the string with a “Yes” or “No” value. Here is an example:
Notice how there are three double-quote marks in the first FIND argument? This is because the middle " is being ignored due to the presence of the backslash immediately preceding it. That means that while the first " is marking the beginning of the text string, it is the third " that closes the string. Thanks to the escape character, the formula ignores the special meaning the second " would normally carry.
Escaping the Escape Character
Sooner or later, the question arises: If a backslash is an escape character and invalidates the double-quotation mark that follows it, how do I perform a search for a backslash in text?
This question usually arises when someone tries to adapt the example statement above to determine if a backslash character exists in the first column of the dataset using a formula like this:
However, since the backslash escapes the double-quote, the string to be found doesn't actually close until the NEXT double quote (prior to the word "Yes"). In this case, the backslash as an escape character needs to itself be escaped so that is parsed as text into the FIND function, like so:
Warning: In regular expressions, there are 12 characters with special meanings: ^ $ . | ? * + ( ) [ and open curly brace. If you want to search for those actual characters and not their special meanings, add a double backslash (not a single backslash) before it. For example, to search for asterisk characters with a regular expression, type"\\*" not "*". To search for a backslash character with a regular expression, type four backslash characters.
In order to specify a character class within a Regular Expression, you will also need to escape the sequence. For example, the character class for a digit (effectively [0-9]) is \d, when using this character class in a computed field you would also need to escape it, becoming \\d. Here is a list of character classes:
\s White space \S Not white space \d Digit \D Not digit \w Word \W Not word \x Hex digit \O Octal digit