LEFT scalar function

The LEFT function returns the leftmost string of string-expression of length length, expressed in the specified string unit.

The schema is SYSIBM. The SYSFUN version of the LEFT function continues to be available.

string-expression

An expression that specifies the string from which the result is derived. The expression must return a built-in string, numeric value, Boolean value, or datetime value. If the value is not a string, it is implicitly cast to VARCHAR before the function is evaluated. Zero or more contiguous code points of the string comprise a substring of the string.

The string-expression is padded on the right with the necessary number of padding characters so that the specified substring of string-expression always exists. The character used for padding is the same character that is used to pad the string in contexts where padding would occur. For more information about padding, see String assignments in Assignments and comparisons.

length

An expression that specifies the length of the result. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. length must be greater than or equal to 0 (SQLSTATE 22011). If OCTETS is specified and the result is graphic data, the value must be an even number (SQLSTATE 428GC).

If length is not a constant and a string unit is not specified, length must be less than or equal to the length attribute of string-expression (SQLSTATE 22011).

If length is not a constant and a string unit is specified, length must be less than or equal to the corresponding value from the following table (SQLSTATE 22011):

Table 1. Maximum value of `length` when `length` is not a constant and a string unit is specified
String unit of `string-expression`	Specified string unit	Maximum value of `length` L = length attribute of `string-expression`
OCTETS	OCTETS	L
OCTETS	CODEUNITS16	L/2
OCTETS	CODEUNITS32	L/4
CODEUNITS16	OCTETS	L*2
CODEUNITS16	CODEUNITS16	L
CODEUNITS16	CODEUNITS32	L/2
CODEUNITS32	OCTETS	L*4
CODEUNITS32	CODEUNITS16	L*2
CODEUNITS32	CODEUNITS32	L

If length is a constant and the data type of string-expression is:

CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC: length must be less than or equal to 32,672 OCTETS, 16,336 CODEUNITS16 or 8168 CODEUNITS32 (SQLSTATE 22011).
CLOB or DBCLOB: length must be less than or equal to 2,147,483,647 OCTETS, 1,073,741,823 CODEUNITS16, or 53,6870,911 CODEUNITS32 (SQLSTATE 22011).
BLOB: length must be less than or equal to 2,147,483,647 OCTETS (SQLSTATE 22011).

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit of length.

CODEUNITS16 specifies that length is expressed in 16-bit UTF-16 code units. CODEUNITS32 specifies that length is expressed in 32-bit UTF-32 code units. OCTETS specifies that length is expressed in bytes.

If the string unit is specified as CODEUNITS16 or CODEUNITS32, and string-expression is a binary string or bit data, an error is returned (SQLSTATE 428GC). If the string unit is specified as OCTETS and string-expression is a graphic string, length must be an even number; otherwise, an error is returned (SQLSTATE 428GC). If a string unit is not explicitly specified, the string unit of string-expression determines the unit that is used. For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String units in built-in functions in Character strings.

Result

The result of the function is a varying-length string that depends on the data type of string-expression:

VARCHAR if string-expression is CHAR or VARCHAR
CLOB if string-expression is CLOB
VARGRAPHIC if string-expression is GRAPHIC or VARGRAPHIC
DBCLOB if string-expression is DBCLOB
VARBINARY if string-expression is BINARY or VARBINARY
BLOB if string-expression is BLOB

The string unit of the result is the string unit of string-expression.The length attribute of the result depends on how length and string unit are specified:

If length is not a constant, then the length attribute of the result is the same as the length attribute of string-expression.
If length is a constant and a string unit is not specified, then the length attribute of the result is the maximum of length and the length attribute of string-expression.

If length is a constant and a string unit is specified, then the length attribute of the result is shown in Table 2:

Table 2. Length attribute of result when `length` is a constant and a string unit is specified
String unit of `string-expression`	Specified string unit	Maximum value of `length` L = length attribute of `string-expression`
OCTETS	OCTETS	max(L, `length`)
OCTETS	CODEUNITS16	max(L, `length` * 2)
OCTETS	CODEUNITS32	max(L, `length` * 4)
CODEUNITS16	OCTETS	max(L, `length` / 2)
CODEUNITS16	CODEUNITS16	max(L, `length`)
CODEUNITS16	CODEUNITS32	max(L, `length` * 2)
CODEUNITS32	OCTETS	max(L, `length` / 4)
CODEUNITS32	CODEUNITS16	max(L, `length` / 2)
CODEUNITS32	CODEUNITS32	max(L, `length`)

The actual length of the result (in string units) is length.

If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples

Example 1: Assume that variable ALPHA has a value of ABCDEF. The following statement:
```
   SELECT LEFT(ALPHA,3)
     FROM SYSIBM.SYSDUMMY1
```
returns ABC, which are the three leftmost characters in ALPHA.
Example 2: Assume that variable NAME, which is defined as VARCHAR(50), has a value of KATIE AUSTIN, and that the integer variable FIRSTNAME_LEN has a value of 5. The following statement:
```
   SELECT LEFT(NAME,FIRSTNAME_LEN)
     FROM SYSIBM.SYSDUMMY1
```
returns the value KATIE.
Example 3: The following statement returns a zero-length string.
```
   SELECT LEFT('ABCABC',0)
     FROM SYSIBM.SYSDUMMY1
```
Example 4: The FIRSTNME column in the EMPLOYEE table is defined as VARCHAR(12). Find the first name of an employee whose last name is BROWN and return the first name in a 10-byte string.
```
   SELECT LEFT(FIRSTNME, 10)
     FROM EMPLOYEE
     WHERE LASTNAME = 'BROWN'
```
returns a VARCHAR(12) string that has the value DAVID followed by five blank characters.

Example 5: In a Unicode database, FIRSTNAME is a VARCHAR(12) column. One of its values is the 6-character string Jürgen. When FIRSTNAME has this value:

   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

Example 6: The following example works with the Unicode string &N~AB, where & is the musical symbol G clef character, and ~ is the combining tilde character. This string is shown in different Unicode encoding forms in the following example:

& N ~ A B

UTF-8 X'F09D849E' X'4E' X'CC83' X'41' X'42'

UTF-16BE X'D834DD1E' X'004E' X'0303' X'0041' X'0042'
Assume that the variable UTF8_VAR, with a length attribute of 20 bytes, contains the UTF-8 representation of the string.
```
   SELECT LEFT(UTF8_VAR, 2, CODEUNITS16),
     LEFT(UTF8_VAR, 2, CODEUNITS32),
     LEFT(UTF8_VAR, 2, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values &, &N, and bb, respectively, where b represents the blank character.
```
   SELECT LEFT(UTF8_VAR, 5, CODEUNITS16),
     LEFT(UTF8_VAR, 5, CODEUNITS32),
     LEFT(UTF8_VAR, 5, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values &N~A, &N~AB, and &N, respectively.
```
   SELECT LEFT(UTF8_VAR, 10, CODEUNITS16),
     LEFT(UTF8_VAR, 10, CODEUNITS32),
     LEFT(UTF8_VAR, 10, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values &N~ABbbbb, &N~ABbbbbb, and &N~ABb, respectively, where b represents the blank character.
Assume that the variable UTF16_VAR, with a length attribute of 20 code units, contains the UTF-16BE representation of the string.
```
   SELECT LEFT(UTF16_VAR, 2, CODEUNITS16),
     LEFT(UTF16_VAR, 2, CODEUNITS32),
     HEX (LEFT(UTF16_VAR, 2, OCTETS))
   FROM SYSIBM.SYSDUMMY1
```
returns the values &, &N, and X'D834', respectively, where X'D834' is an unmatched high surrogate.
```
   SELECT LEFT(UTF16_VAR, 5, CODEUNITS16),
     LEFT(UTF16_VAR, 5, CODEUNITS32),
     LEFT(UTF16_VAR, 6, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values &N~A, &N~AB, and &N, respectively.
```
   SELECT LEFT(UTF16_VAR, 10, CODEUNITS16),
     LEFT(UTF16_VAR, 10, CODEUNITS32),
     LEFT(UTF16_VAR, 10, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values &N~ABbbbb, &N~ABbbbbb, and &N~A, respectively, where b represents the blank character.

	&	N	~	A	B
UTF-8	X'F09D849E'	X'4E'	X'CC83'	X'41'	X'42'
UTF-16BE	X'D834DD1E'	X'004E'	X'0303'	X'0041'	X'0042'