Skip to main content
Previous section

String Manipulation

String manipulation functions and operators.

Description

InterSystems 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.

InterSystems SQL supports string functions, string condition expressions, and string operators.

ObjectScript 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. InterSystems SQL provides numerous letter case and collation functions and operators.

When a string is specified for a numeric argument, most InterSystems 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.

You can perform a word-aware search of a string for specified words or phrases, including wildcard searching. For further details refer to Using InterSystems SQL Search.