LEFT

The LEFT function returns a string that consists of the specified number of leftmost bytes of the specified string units.

Character string:

The LEFT function returns the leftmost string of character-expression, graphic-expression, or binary-expression consisting of length of the string units that are specified implicitly or explicitly.

character-expression
An expression that specifies the string from which the result is derived. The string must be a character string. A substring of character-expression is zero or more contiguous code points of character-expression.

The string can contain mixed data. Depending on the units that are specified to evaluate the function, the result is not necessarily a properly formed mixed data character string.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

length
An expression that specifies the length of the result. The value must be an integer between 0 and n, where n is the length attribute of character-expression, expressed in the units that are either implicitly or explicitly specified.

The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) which is then assigned to an INTEGER value.

The character-expression, graphic-expression, or binary-expression is effectively padded on the right with the necessary number of padding characters so that the specified substring of the expression always exists. The encoding scheme of the data determines the padding character:

  • For ASCII SBCS data or ASCII mixed data, the padding character is X'20'.
  • For ASCII DBCS data, the padding character depends on the CCSID; for example, for Japanese (CCSID 301) the padding character is X'8140', while for simplified Chinese it is X'A1A1'.
  • For EBCDIC SBCS data or EBCDIC mixed data, the padding character is X'40'.
  • For EBCDIC DBCS data, the padding character is X'4040'.
  • For Unicode SBCS data or UTF-8 (Unicode mixed data), the padding character is X'20'.
  • For UTF-16 (Unicode DBCS) data, the padding character is X'0020'.
  • For binary data, the padding character is X'00'.

The result of the function is a varying-length string with a length attribute that is the same as the length attribute of the first expression and a data type that depends on the data type of the expression:

  • VARCHAR if character-expression is CHAR or VARCHAR
  • CLOB if character-expression is CLOB
  • VARGRAPHIC if graphic-expression is GRAPHIC or VARGRAPHIC
  • DBCLOB if graphic-expression is DBCLOB
  • VARBINARY if binary-expression is BINARY or VARBINARY
  • BLOB if binary-expression is BLOB

The actual length of the result is determined from length.

The CCSID of the result is the same as that of the first expression.

Example:

Assume that the column EMPL_NAME has a value "Richard". The following example returns "Ric" which are the three leftmost characters.


SELECT LEFT(EMPL_NAME, 3) FROM EMPLOYEE