Caché SQL Reference
String Manipulation
[Home] [Back] 
InterSystems: The power behind what matters   
Class Reference   

String manipulation functions and operators.
Caché SQL provides support for several types of string manipulation:
Caché SQL supports string functions, string condition expressions, and string operators.
Caché string manipulation is case-sensitive. Letters in strings can be converted to uppercase, to lowercase, or retained as mixed case. String collation can be case-sensitive, or not case-sensitive; by default, SQL string collation is SQLUPPER which is not case-sensitive. Caché SQL provides numerous letter case and collation functions and operators.
When a string is specified for a numeric argument, most Caché SQL functions perform the following string-to-number conversions: a nonnumeric string is converted to the number 0; a numeric string is converted to a canonical number; and a mixed-numeric string is truncated at the first nonnumeric character and then converted to a canonical number.
String Concatenation
The following functions concatenate substrings into a string:
The concatenate operator (||) can also be used to concatenate two strings.
String Length
The following functions can be used to determine the length of a string:
Truncation and Trimming
The following functions can be used to truncate or trim a string. Truncation limits the length of the string, deleting all characters beyond the specified length. Trimming deletes leading and/or trailing blank spaces from a string.
Substring Search
The following functions search for a substring within a string and return a string position:
The following functions search for a substring by position or delimiter within a string and return the substring:
The contains operator ([) can also be used to determine if a substring appears in a string.
The %STARTSWITH comparison operator matches the specified character(s) against the beginning of a string.
Substring Search–and–Replace
The following functions search for a substring within a string and replace it with another substring.
Character-Type and Word-Aware Comparisons
The %PATTERN comparison operator matches a string to a specified pattern of character types.
The %CONTAINS and %CONTAINSTERM comparison operators perform a word-aware search of a string for specified words or phrases.