String Operators

The following operators perform string operations.

? & ?

(String) & (String)

Appends the second String onto the first String.

Example: Assume that String1 has a value of "Hokey" and String2 has value of "Pokey". (String1) & (String2) results in a String value of "HokeyPokey".

SQLStr()

SQLStr (String or DateTime or Integer or Numeric or Boolean)

Converts the input value, which can be any Normal data types (except handle types), into a properly formatted string suitable for direct insertion into a SQL command. The behavior depends on the input data type.

Examples:

String: Doubles any embedded single quotes, then single quote surrounds the result before returning. For example:

Input: Fred's Bar and Grill

Output: 'Fred''s Bar and Grill'

This also eliminates some messy single quote manipulations. For example, the following expression:

"WHERE LastName = ‘" & myLastNameVar & "’"

can be replaced with:

"WHERE LastName = " & SQLStr(myLastNameVar)

This last expression is also better since it will correctly handle embedded single quotes.

DateTime: Returns an ODBC timestamp escape string of the form:

{ts 'YYYY-MM-DD hh:mm:ss'} 

Using SQLStr ensures portability across RDBMSs (as long as the ODBC driver supports timestamp escape processing – which most do nowadays).

Integer: Returns the string representation of the number.

Numeric: Returns the string representation of the number, using the default precision and scale of the Java Double.toString() function. This will work for most numeric types; however, if you need to provide a particular precision and/or scale, then SQLStr cannot be used (i.e. you must perform the conversion manually).

Boolean: Returns 1 if true and 0 if false. This works when the RDBMS type is a numeric or bit type; however, if the type is represented as a character, then SQLStr cannot be used (i.e. you must perform the conversion manually).

StrEqlNoCase

StrEqlNoCase (string, string)

Performs a case insensitive comparison of the two strings.

Example: A condition step with the condition, StrEqlNoCase("IAmSoCool","iamsocool") will take the True exit path.

StrLeft

StrLeft (String, Integer)

Returns a specific number of letters from a String value starting from the left.

Example: StrLeft ("Elephant", 3) returns a string with a value of "Ele".

StrLen

StrLen (String)

Determines the number of characters in a string value.

Example: StrLen ("Coffee") results in an integer with a value of six.

StrLower

StrLower (String)

Converts the letters of a String value to all lowercase.

Example: StrLower ("John Doe") results in a String with a value of "john doe".

StrMid

StrMid (String, Integer, Integer)

Returns the characters between two positions.

The first integer specifies the starting position. (0 is the first position.)

The second integer specified the ending position.

Note: If the string is shorter than the second (ending) position, only the length of the string is returned.

Example: StrMid ("Coffee", 2, 3) returns a String with a value of "ff".

StrMid ("Submarine", 0, 6) returns a String with a value of "Submari".

StrMid("coffee", 2, 10) returns a String with a value of "ffee"

StrPos

StrPos (String, String, Integer)

Finds the first occurrence of a String value within a String value starting at a specific position (the first position is zero). If the string is not found, StrPos returns -1.

Example: StrPos ("Coffee", "f", 0) results in an Integer value of 2. The first "f" occurs at position two in the String "Coffee".

StrRight

StrRight (String, Integer)

Returns a specific number of letters from a String value starting from the right.

Example: StrRight ("Elephant", 3) returns a String with a value of "ant".

StrTrim

StrTrim (String)

Removes any leading and trailing spaces from a String value.

Example: StrTrim(" 123 456 ") would result in the value "123 456".

StrUpper

StrUpper (String)

Converts the letters of a String value to all upper case.

Example: StrUpper ("John Doe") results in a String with a value of "JOHN DOE".

SubstStr

SubstStr (String1,Sring2,String3)

Substitutes String3 for String2 if String2 occurs inside of String1.

String1 is the quoted string or string variable to search.

String2 is the quoted string or string variable to search for in String1.

String3 is the quoted string or string variable to replace String2 in String1.

Example: SubstStr("Welcome to Interactive Marketing","Marketing","Research") returns "Welcome to Interactive Research".

SubstStrRegEx

SubstStr (String1,RegEx,String3)

Substitutes String3 for any matches found by the RegEx (regular expression) inside of String1.

String1 is the quoted string or string variable to search.

RegEx is the regular expression used to search for a sub-string in String1.

String3 is the quoted string or string variable to replace any match found by the RegEx in String1.

Example: SubstStrRegEx("What type of value is this: 12.202.48.80", "\\b\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\b", "IP Address") returns a value of "What type of value is this: IP Address".

Note: To get a single slash in the actual string at runtime, you need to enter double slashes. For example, to get the string "\+" you need to enter "\\+" in the Interaction Designer editor.