Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Previous section

String Manipulation

String manipulation functions and operators.

Description

Caché SQL provides support for several types of string manipulation:

  • Strings can be manipulated by length, character position, or substring value.

  • Strings can be manipulated by a designated delimiter character or delimiter string.

  • Strings can tested by pattern matching and word-aware searches.

  • Specially encoded strings, called lists, contain embedded substring identifiers without using a delimiter character. The various $LIST functions operate on these encoded character strings, which are incompatible with standard character strings. The only exceptions are the $LISTGET function and the one-argument and two-argument forms of $LIST, which take an encoded character string as input, but output a single element value as a standard character string.

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:

  • CONCAT: concatenates two substrings, returns a single string.

  • STRING: concatenates two or more substrings, returns a single string.

  • XMLAGG: concatenates all of the values of a column, returns a single string. For further details, see Aggregate Functions.

  • LIST: concatenates all of the values of a column, including a comma delimiter, returns a single string. For further details, see Aggregate Functions.

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:

  • CHARACTER_LENGTH and CHAR_LENGTH: return the number of characters in a string, including trailing blanks. NULL returns NULL.

  • LENGTH: returns the number of characters in a string, excluding trailing blanks. NULL returns NULL.

  • $LENGTH: returns the number of characters in a string, including trailing blanks. NULL is returned as 0.

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:

  • POSITION: searches by substring value, finds first match, returns position of beginning of substring.

  • CHARINDEX: searches by substring value, finds first match, returns position of beginning of substring. Starting point can be specified.

  • $FIND: searches by substring value, finds first match, returns position of end of substring. Starting point can be specified.

  • INSTR: searches by substring value, finds first match, returns position of beginning of substring. Both starting point and substring occurrence can be specified.

The following functions search for a substring by position or delimiter within a string and return the substring:

  • $EXTRACT: searches by string position, returns substring specified by start position, or start and end positions. Searches from beginning of string.

  • SUBSTRING: searches by string position, returns substring specified by start position, or start and length. Searches from beginning of string.

  • SUBSTR: searches by string position, returns substring specified by start position, or start and length. Searches from beginning or end of string.

  • $PIECE: searches by delimiter character, returns first delimited substring. Starting point can be specified or defaults to beginning of string.

  • $LENGTH: searches by delimiter character, returns the number of delimited substrings. Searches from beginning of string.

  • $LIST: searches by substring count on a specially encoded list string. It locates a substring by substring count and returns the substring value. Searches from beginning of string.

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.

  • REPLACE: searches by string value, replaces substring with new substring. Searches from beginning of string.

  • STUFF: searches by string position and length, replaces substring with new substring. Searches from beginning of string.

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.