String Functions

String functions perform operations on text and string values. Most string functions return a string data type, though some (like Length and Find) return numbers, and others (like IsNullOrEmpty) return Boolean values. When non-string values are passed to these functions, they are automatically interpreted as strings.

LCase function

The LCase function converts all uppercase letters in a string to lowercase. Non-alphabetic characters remain unchanged.

This function returns a String data type.

Syntax

LCase(exprAsString)

Parameter	Description
exprAsString	Required. The string expression to convert to lowercase.

Examples

1. Convert a mixed-case string to lowercase:

   LCase("Hello WORLD")

   Result: "hello world"

2. Normalize email addresses for comparison:

   LCase(|->[Email])

   If Email = "John.Doe@Example.COM":

   Result: "john.doe@example.com"

3. Create a lowercase URL slug from a name:

   LCase(FindAndReplace(|->[ProductName], " ", "-"))

   If ProductName = "Premium Widget":

   Result: "premium-widget"

UCase function

The UCase function converts all lowercase letters in a string to uppercase. Non-alphabetic characters remain unchanged.

This function returns a String data type.

Syntax

UCase(exprAsString)

Parameter	Description
exprAsString	Required. The string expression to convert to uppercase.

Examples

1. Convert a mixed-case string to uppercase:

   UCase("Hello WORLD")

   Result: "HELLO WORLD"

2. Format a state abbreviation:

   UCase(|->[State])

   If State = "ca":

   Result: "CA"

3. Create an uppercase header from recipient data:

   UCase(|->[LastName]) & ", " & |->[FirstName]

   If LastName = "Smith" and FirstName = "John":

   Result: "SMITH, John"

TCase function

The TCase function converts the first letter of each word to uppercase and all remaining letters to lowercase (title case). This is useful for normalizing names and titles that may have inconsistent capitalization.

This function returns a String data type.

Syntax

TCase(exprAsString)

Parameter	Description
exprAsString	Required. The string expression to convert to title case.

Examples

1. Convert a mixed-case string to title case:

   TCase("Hello WORLD")

   Result: "Hello World"

2. Normalize a recipient's name:

   TCase(|->[FirstName] & " " & |->[LastName])

   If FirstName = "JOHN" and LastName = "DOE":

   Result: "John Doe"

3. Format a city name from all-caps data:

   TCase(|->[City])

   If City = "NEW YORK":

   Result: "New York"

4. Create a properly formatted address line:

   TCase(|->[Street] & ", " & |->[City]) & ", " & UCase(|->[State])

   If Street = "123 MAIN STREET", City = "BOSTON", State = "ma":

   Result: "123 Main Street, Boston, MA"

Length function

The Length function returns the number of characters in a string, including spaces, punctuation, and special characters.

This function returns a Number data type.

Syntax

Length(exprAsString)

Parameter	Description
exprAsString	Required. The string expression whose length is to be calculated.

Examples

1. Get the length of a string:

   Length("Hello WORLD")

   Result: 11

2. Check if a field exceeds a maximum length:

   If(Length(|->[Comments]) > 100) {"Comment truncated"} else {|->[Comments]}

   If Comments length is over 100 characters:

   Result: "Comment truncated" will be displayed.

   If Comments length is less than or equal to 100 characters:

   Result: The value of Comments will be returned.

3. Calculate padding needed to right-align a value:

   20 - Length(|->[ProductCode])

   If ProductCode = "ABC123":

   Result: 14

4. Validate a phone number has the expected length:

   If(Length(CleanNumber(|->[Phone])) = 10) {"Valid"} else {"Invalid"}

IsNullOrEmpty function

The IsNullOrEmpty function determines whether a string is null or empty (zero-length). This is useful for checking if a field contains any data before using it.

This function returns a Boolean data type.

Syntax

IsNullOrEmpty(exprAsString)

Parameter	Description
exprAsString	Required. The string expression to test for null or empty value.

Examples

1. Check if a string is empty:

   IsNullOrEmpty("")

   Result: True

   IsNullOrEmpty("Hello")

   Result: False

2. Check if a recipient field has a value:

   IsNullOrEmpty(|->[MiddleName])

   If MiddleName = "":

   Result: True

3. Conditionally include a middle initial:

   |->[FirstName] & If(IsNullOrEmpty(|->[MiddleName])) {""} else {" " & SubString(|->[MiddleName], 0, 1) & "."} & " " & |->[LastName]

   If FirstName = "John", MiddleName = "Robert", LastName = "Smith":

   Result: "John R. Smith"

4. Provide a default value when field is empty:

   If(IsNullOrEmpty(|->[Nickname])) {|->[FirstName]} else {|->[Nickname]}

   If FirstName = "John", Nickname = "":

   Result: John

   If FirstName = "Janet", Nickname = "Jan":

   Result: Jan

5. Conditionally include a prefix if the mobile number is provided:

   if (!IsNullOrEmpty(|->[mobile]))
   {
   "Mobile: " & |->[mobile]
   }
   else
   {
   ""
   }

   If mobile = "(123) 555-1234":

   Result: "Mobile: (123) 555-1234"

   If mobile = "":

   Result: ""

SubString function

The SubString function extracts a portion of a string based on a starting position and character count. This is useful for extracting specific parts of a string.

This function returns a String data type.

Syntax

SubString(exprAsString, startPosition, characterCount)

Parameter	Description
exprAsString	Required. The string from which to extract characters.
startPosition	Required. The zero-based starting position (0 = first character).
characterCount	Required. The number of characters to extract. Use -1 to extract all remaining characters from the start position.

Examples

1. Extract the first 5 characters:

   SubString("Hello WORLD", 0, 5)

   Result: "Hello"

2. Extract characters from the middle of a string:

   SubString("Hello WORLD", 6, 5)

   Result: "WORLD"

3. Extract all characters from a position to the end:

   SubString("Hello WORLD", 6, -1)

   Result: "WORLD"

4. Get the first initial from a name:

   SubString(|->[FirstName], 0, 1)

   If FirstName = "John":

   Result: "J"

5. Extract area code from a phone number:

   SubString(CleanNumber(|->[Phone]), 0, 3)

   If Phone = "(555) 123-4567":

   Result: "555"

6. Return the first sentence from a comment, by selecting all text from the start (0) position until the location of the first full stop / period (.):

   SubString(|->[comment], 0, Find(|->[comment], ".", 0) + 1)

   If comment = "Etiam justo. Etiam pretium iaculis justo. In hac habitasse platea dictumst. Etiam faucibus cursus urna.":

   Result: "Etiam justo."

Trim function

The Trim function removes whitespace characters (spaces, tabs) from both the beginning and end of a string. Whitespace within the string is not affected.

This function returns a String data type.

Syntax

Trim(exprAsString)

Parameter	Description
exprAsString	Required. The string from which to remove leading and trailing whitespace.

Examples

1. Remove whitespace from both ends:

   Trim(" Hello WORLD ")

   Result: "Hello WORLD"

2. Clean up recipient data that may have extra spaces:

   Trim(|->[FirstName] & " " & |->[LastName])

   If FirstName = " John " and LastName = " Smith ":

   Result: "John Smith"

3. Ensure clean data before comparison:

   If(Trim(|->[Status]) = "Active") {"Yes"} else {"No"}

LTrim function

The LTrim function removes whitespace characters from the beginning (left side) of a string only. Trailing whitespace is preserved.

This function returns a String data type.

Syntax

LTrim(exprAsString)

Parameter	Description
exprAsString	Required. The string from which to remove leading whitespace.

Examples

1. Remove leading whitespace only:

   LTrim(" Hello WORLD ")

   Result: "Hello WORLD "

2. Clean up left-padded data:

   LTrim(|->[AccountNumber])

   If AccountNumber = " 12345":

   Result: "12345"

RTrim function

The RTrim function removes whitespace characters from the end (right side) of a string only. Leading whitespace is preserved.

This function returns a String data type.

Syntax

RTrim(exprAsString)

Parameter	Description
exprAsString	Required. The string from which to remove trailing whitespace.

Examples

1. Remove trailing whitespace only:

   RTrim(" Hello WORLD ")

   Result: " Hello WORLD"

2. Clean up right-padded fixed-width data:

   RTrim(|->[Description])

   If Description = "Widget ":

   Result: "Widget"

Find function

The Find function searches for a substring within a string and returns its zero-based position. If the substring is not found, the function returns -1. The search is case-sensitive.

This function returns a Number data type.

Syntax

Find(exprAsString, searchString, startPosition)

Parameter	Description
exprAsString	Required. The string to search within.
searchString	Required. The substring to find.
startPosition	Required. The zero-based position at which to begin the search.

Examples

1. Find the position of a substring:

   Find("Hello WORLD", "WORLD", 0)

   Result: 6

2. Search is case-sensitive:

   Find("Hello WORLD", "world", 0)

   Result: -1 (not found)

3. Check if a string contains a specific value:

   If(Find(|->[Email], "@", 0) > -1) {"Found @ symbol"} else {"Missing @ symbol"}

4. Find the second occurrence by starting after the first:

   Find("apple,banana,apple", "apple", 1)

   Result: 13

5. Extract domain from email address:

   SubString(|->[Email], Find(|->[Email], "@", 0) + 1, -1)

   If Email = "john@example.com":

   Result: "example.com"

Replace function

The Replace function replaces a specified number of characters at a given position with a new string. This is useful for inserting or overwriting characters at specific locations within a string.

This function returns a String data type.

Syntax

Replace(exprAsString, insertString, startPosition, charactersToReplace)

Parameter	Description
exprAsString	Required. The original string to modify.
insertString	Required. The string to insert at the specified position.
startPosition	Required. The zero-based position at which to begin replacing.
charactersToReplace	Required. The number of characters to remove before inserting. Use 0 to insert without removing.

Examples

1. Replace characters at a specific position:

   Replace("Hello WORLD", "UNIVERSE", 6, 5)

   Result: "Hello UNIVERSE"

2. Insert characters without removing any (set charactersToReplace to 0):

   Replace("HelloWorld", " ", 5, 0)

   Result: "Hello World"

3. Mask part of a credit card number:

   Replace(|->[CardNumber], "****-****-****-", 0, 15)

   If CardNumber = "1234-5678-9012-3456":

   Result: "****-****-****-3456"

FindAndReplace function

The FindAndReplace function searches for all occurrences of a substring and replaces them with a new value. The search is case-sensitive.

This function returns a String data type.

Syntax

FindAndReplace(exprAsString, searchString, replacementString)

Parameter	Description
exprAsString	Required. The string to search within.
searchString	Required. The substring to find.
replacementString	Required. The string to replace each occurrence with.

Examples

1. Replace a word in a string:

   FindAndReplace("Hello WORLD", "WORLD", "UNIVERSE")

   Result: "Hello UNIVERSE"

2. Remove a substring by replacing with empty string:

   FindAndReplace("Hello WORLD", " WORLD", "")

   Result: "Hello"

3. Replace spaces with hyphens for a URL:

   FindAndReplace(|->[ProductName], " ", "-")

   If ProductName = "Premium Widget Set":

   Result: "Premium-Widget-Set"

4. Standardize data by replacing abbreviations:

   FindAndReplace(FindAndReplace(|->[Address], "St.", "Street"), "Ave.", "Avenue")

5. Remove currency symbol before numeric conversion:

   AsNumber(FindAndReplace(|->[Price], "$", ""))

   If Price = "$99.99":

   Result: 99.99

FindAndReplaceChars function

The FindAndReplaceChars function replaces all instances of any character from a specified set with a replacement string. Unlike FindAndReplace, this function treats the search parameter as individual characters, not as a substring.

This function returns a String data type.

Syntax

FindAndReplaceChars(exprAsString, charactersToReplace, replacementString)

Parameter	Description
exprAsString	Required. The string to search within.
charactersToReplace	Required. A string containing all characters to be replaced (each character is treated individually).
replacementString	Required. The string to replace each matching character with.

Examples

1. Remove phone number formatting characters:

   FindAndReplaceChars(|->[Phone], "()- ", "")

   If Phone = "(555) 123-4567":

   Result: "5551234567"

2. Remove punctuation marks:

   FindAndReplaceChars("Hello, World! How are you?", ",.!?", "")

   Result: "Hello World How are you"

3. Sanitize a filename by replacing invalid characters:

   FindAndReplaceChars(|->[FileName], "/\\:*?\"<>|", "_")

   If FileName = "Report: Q1/Q2":

   Result: "Report_ Q1_Q2"

4. Remove vowels from a string:

   FindAndReplaceChars("Hello World", "aeiouAEIOU", "")

   Result: "Hll Wrld"

FindByRegExp function

The FindByRegExp function searches a string using a regular expression pattern and returns the matching text. This provides powerful pattern matching capabilities for complex text extraction.

This function returns a String data type.

Syntax

FindByRegExp(exprAsString, regExpPattern, firstMatchOnly)

Parameter	Description
exprAsString	Required. The string to search within.
regExpPattern	Required. The regular expression pattern to match.
firstMatchOnly	Required. Set to True to return only the first match, or False to return all matches concatenated.

Note: The regular expression grammar used is ECMA-262 on Windows with modifications and Extended on Macintosh.

Examples

1. Extract the first word from a string:

   FindByRegExp("Hello WORLD", "[A-Za-z]+", True)

   Result: "Hello"

2. Extract an email address from text:

   FindByRegExp(|->[Notes], "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}", True)

   If Notes = "Contact me at john@example.com for details":

   Result: "john@example.com"

3. Extract all numbers from a string:

   FindByRegExp("Order 123 has 5 items", "[0-9]+", False)

   Result: "1235"

4. Extract a ZIP code from an address:

   FindByRegExp(|->[Address], "[0-9]{5}(-[0-9]{4})?", True)

   If Address = "123 Main St, Boston, MA 02101-1234":

   Result: "02101-1234"

FindAndReplaceByRegExp function

The FindAndReplaceByRegExp function searches for text matching a regular expression pattern and replaces it with a specified string. This provides powerful pattern-based find and replace capabilities.

This function returns a String data type.

Syntax

FindAndReplaceByRegExp(exprAsString, regExpPattern, replacementString, firstMatchOnly)

Parameter	Description
exprAsString	Required. The string to search within.
regExpPattern	Required. The regular expression pattern to match.
replacementString	Required. The string to replace each match with.
firstMatchOnly	Required. Set to True to replace only the first match, or False to replace all matches.

Examples

1. Replace the first word in a string:

   FindAndReplaceByRegExp("Hello WORLD", "[A-Za-z]+", "Hi", True)

   Result: "Hi WORLD"

2. Remove all digits from a string:

   FindAndReplaceByRegExp("abc123def456", "[0-9]+", "", False)

   Result: "abcdef"

3. Mask all email addresses in text:

   FindAndReplaceByRegExp(|->[Comments], "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}", "[email hidden]", False)

4. Remove non-digits from a formatted phone number:

   FindAndReplaceByRegExp(|->[Phone], "[^0-9]", "", False)

   If Phone = "+1 (555) 123-4567":

   Result: "15551234567"

5. Replace multiple spaces with a single space:

   FindAndReplaceByRegExp("Hello World", " +", " ", False)

   Result: "Hello World"

CleanNumber function

The CleanNumber function removes all non-digit characters from a string, keeping only the digits 0-9. This is useful for cleaning phone numbers, account numbers, or any numeric data that may contain formatting characters.

This function returns a String data type (not a number), which preserves leading zeros and allows for sequences longer than 14 digits.

Syntax

CleanNumber(exprAsString)

Parameter	Description
exprAsString	Required. The string from which to extract digits.

Examples

1. Remove formatting from a phone number:

   CleanNumber("+1 888-452-1111")

   Result: "18884521111"

2. Clean a formatted social security number:

   CleanNumber("123-45-6789")

   Result: "123456789"

3. Extract digits from a currency value:

   CleanNumber("$1,234.56")

   Result: "123456"

4. Prepare a phone number for reformatting:

   FormatNumber(CleanNumber(|->[Phone]), "(###) ###-####")

   If Phone = "555.123.4567":

   Result: "(555) 123-4567"

Note 1: Because CleanNumber returns a string, it can handle numbers longer than 14 digits (the limit for numeric calculations in QLingo). This makes it suitable for phone numbers, credit card numbers, and other long numeric identifiers.

Note 2: CleanNumber will remove thousand separators and decimals!

CleanRecipientKey function

The CleanRecipientKey function removes or replaces characters that are unsafe for use in URLs and recipient keys. This includes spaces, quotes, colons, and other special characters that could cause issues in personalized URLs (PURLs).

This function returns a String data type.

Syntax

CleanRecipientKey(exprAsString [, replaceInvalidWith])

Parameter	Description
exprAsString	Required. The string to clean for use as a recipient key.
replaceInvalidWith	Optional. The string to replace invalid characters with. If omitted, invalid characters are removed.

Examples

1. Replace spaces with underscores:

   CleanRecipientKey("John Michael.Smith", "_")

   Result: "John_Michael.Smith"

2. Remove invalid characters entirely:

   CleanRecipientKey("John's Key: 123")

   Result: "JohnsKey123"

3. Create a clean PURL key from recipient name:

   CleanRecipientKey(|->[FirstName] & |->[LastName] & |->[ID], "-")

   If FirstName = "John", LastName = "O'Brien", ID = "123":

   Result: "JohnO-Brien123"

4. Generate a URL-safe identifier:

   LCase(CleanRecipientKey(|->[CompanyName], "-"))

   If CompanyName = "Acme & Sons, Inc.":

   Result: "acme---sons--inc."

SecureID function

The SecureID function generates a unique, cryptographically secure identifier that cannot be guessed. This is useful for creating secure recipient keys for personalized URLs where predictability would be a security concern.

This function returns a String data type.

Syntax

SecureID()

This function takes no parameters.

Examples

1. Generate a secure identifier:

   SecureID()

   Result: "5ADCF67B419A4AD796DA4458D25A038E" (example - actual value will vary)

2. Create a secure PURL for sensitive applications:

   "https://example.com/offer/" & SecureID()

   Result: "https://example.com/offer/5ADCF67B419A4AD796DA4458D25A038E"

3. Combine with recipient data for a traceable but secure key:

   |->[CustomerID] & "-" & SecureID()

   If CustomerID = "C1001":

   Result: "C1001-5ADCF67B419A4AD796DA4458D25A038E"

Note: Unlike sequential IDs or IDs based on recipient data, SecureID values cannot be predicted or enumerated. This prevents unauthorized access to personalized content by guessing URLs.

HexToUnicode function

The HexToUnicode function converts hexadecimal values to their corresponding Unicode characters. This is useful for inserting special characters, symbols, or characters from non-Latin scripts.

This function returns a String data type.

Syntax

HexToUnicode(exprAsHexString)

Parameter	Description
exprAsHexString	Required. A string of hexadecimal characters. Groups of four characters represent one Unicode character. Shorter groups are padded with leading zeros.

Examples

1. Insert the Euro symbol:

   HexToUnicode("20AC")

   Result: "€"

2. Insert the copyright symbol:

   HexToUnicode("00A9")

   Result: "©"

3. Insert a trademark symbol:

   HexToUnicode("2122")

   Result: "™"

4. Create a formatted price with currency symbol:

   HexToUnicode("00A3") & FormatNumber(|->[Price], "#,##0.00")

   If Price = 1234.56:

   Result: "£1,234.56"

5. Insert multiple characters:

   HexToUnicode("00A9002000AE")

   Result: "© ®" (copyright, space, registered trademark)

HtmlEncode function

The HtmlEncode function encodes special characters using percent encoding (URL encoding). This converts characters that are unsafe in URLs to their %XX hexadecimal equivalents.

This function returns a String data type.

Syntax

HtmlEncode(exprAsString)

Parameter	Description
exprAsString	Required. The string to encode for safe use in URLs.

Examples

1. Encode a string with special characters:

   HtmlEncode("John&Mary.pdf")

   Result: "John%26Mary.pdf"

2. Encode spaces in a URL parameter:

   HtmlEncode("Hello World")

   Result: "Hello%20World"

3. Build a safe URL with recipient data:

   "https://example.com/search?name=" & HtmlEncode(|->[FullName])

   If FullName = "John O'Brien":

   Result: "https://example.com/search?name=John%20O%27Brien"

4. Encode a file path for download URL:

   "https://example.com/download/" & HtmlEncode(|->[FileName])

   If FileName = "Report Q1 2024.pdf":

   Result: "https://example.com/download/Report%20Q1%202024.pdf"

More topics

• Conversion Functions - Functions for converting between data types

• Numeric Functions - Functions for mathematical operations

• Date Functions - Functions for date and time operations