Substring ([s,e])

The [] (square brackets) extract a substring.

Syntax

Note: In the next(s) syntax diagram(s), the [ ] { } | symbols are part of the syntax.

Single character position form:

variable [ pos ]

Start/end position form:

variable [ start-pos , end-pos ]

variable must be a variable defined with the CHAR or VARCHAR type.
pos defines the position of the character to be extracted.
start-pos defines the position of the first character of the substring to be extracted.
end-pos defines the position of the last character of the substring to be extracted.

Usage

The [] (square brackets) notation following a CHAR or VARCHAR variable extracts a substring from that character variable.

The pos, start-pos and end-pos arguments can be expressed in bytes or characters, depending on the length semantics used in your programs.

Important:

Substring expressions in SQL statements are evaluated by the database server. This may have a different behavior than the substring operator of the language.

Substring expression will return NULL, if there are no characters at the specified positions in the source string. When the source variable is a CHAR, the value is always blank-padded and substring can return spaces. When using a VARCHAR type, the value may have trailing blanks or no characters at the specified position(s). When there are no characters at the specified position(s), substring returns NULL. See the code sample below using CHAR and VARCHAR variables.

Example

MAIN
  DEFINE c5 CHAR(5)
  DEFINE v5 VARCHAR(5)
  LET c5 = "abc"
  LET v5 = "abc"
  DISPLAY "c5[3,3] = [",c5[3,3],"]"
  DISPLAY "v5[2,3] = [",v5[2,3],"]"
  DISPLAY "At position 4, c5 has a space and v5 has nothing:"
  DISPLAY "c5[4] = [",c5[4],"]"
  DISPLAY "c5[4] IS NULL : ", (c5[4] IS NULL)
  DISPLAY "v5[4] = [",v5[4],"]"
  DISPLAY "v5[4] IS NULL : ", (v5[4] IS NULL)
END MAIN

Output:

c5[3,3] = [c]
v5[2,3] = [bc]
At position 4, c5 has a space and v5 has nothing:
c5[4] = [ ]
c5[4] IS NULL :      0
v5[4] = []
v5[4] IS NULL :      1