LEFT

The schema is SYSIBM.

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 string:

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.

If CODEUNITS16 or CODEUNITS32 is specified, see Determining the length attribute of the final result for information about how to calculate the length attribute of the result string.

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the unit that is used to express length. If character-expression is defined as bit data, CODEUNITS16 and CODEUNITS32 cannot be specified.

CODEUNITS16

Specifies that length is expressed in terms of 16-bit UTF-16 code units.

CODEUNITS32

Specifies that length is expressed in terms of 32-bit UTF-32 code units.

OCTETS

Specifies that length is expressed in terms of bytes.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications.

Graphic string:

graphic-expression

An expression that specifies the string from which the result is derived. The string must be a graphic string. A substring of graphic-expression is zero or more contiguous code points of graphic-expression. A partial surrogate character in the expression is replaced with a blank.

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

If CODEUNITS16 or CODEUNITS32 is specified, see Determining the length attribute of the final result for information about how to calculate the length attribute of the result string.

CODEUNITS16 or CODEUNITS32

Specifies the unit that is used to express length.

CODEUNITS16

Specifies that length is expressed in terms of 16-bit UTF-16 code units.

CODEUNITS32

Specifies that length is expressed in terms of 32-bit UTF-32 code units.

For more information about CODEUNITS16 and CODEUNITS32, see String unit specifications.

Binary string:

binary-expression

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

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 binary-expression, expressed in the units that are either implicitly or explicitly specified.

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 result can be null; if any argument is null, the result is the null value.

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

  SELECT LEFT(:ALPHA,3)
    FROM SYSIBM.SYSDUMMY1;

   SELECT LEFT(:NAME, :FIRSTNAME_LEN)
     FROM SYSIBM.SYSDUMMY1;

   SELECT LEFT('ABCABC',0)
     FROM SYSIBM.SYSDUMMY1;

   SELECT LEFT(FIRSTNME,10)
     FROM DSN8A10.EMP
     WHERE LASTNAME='BROWN';

   Function ...                            Returns ...
   LEFT(FIRSTNAME,2,CODEUNITS32)           'Jü'  -- x'4AC3BC'
   LEFT(FIRSTNAME,2,CODEUNITS16)           'Jü'  -- x'4AC3BC'
   LEFT(FIRSTNAME,2,OCTETS)                'J '  -- x'4A20' a truncated string