LOCATE, POSITION, LENGTH, LEFT and RIGHT in DB2

LOCATE

LOCATE returns the position at which the first occurrence of a search string starts within a source string. Schema is SYSIBM.

1
LOCATE(search-string, source-string [, start [, CODEUNITS16 | CODEUNITS32 | OCTETS]])

• search-string — what you are looking for.
• source-string — where you look.
• start — optional 1-based position in the source where the search begins. Omit it to start at the first position.
• CODEUNITS16 / CODEUNITS32 / OCTETS — units for start and the result. CODEUNITS16/32 are not allowed for FOR BIT DATA. OCTETS is not allowed for graphic strings. Binary strings do not take these unit keywords.

Result rules:

• The result is a large integer and can be null (null argument → null result).
• If the search string is found, the result is from 1 to the actual length of the source.
• If it is not found and neither argument is null, the result is 0.
• If search-string has length zero, LOCATE returns 1.

1
2
3
4
5
6
SELECT LOCATE('HAAS', LASTNAME) AS POS
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';   -- 1 if LASTNAME is HAAS
 
SELECT LOCATE('E', 'CHRISTINE', 4) AS NEXT_E
  FROM SYSIBM.SYSDUMMY1;   -- next E at or after position 4

COBOL programmers sometimes expect 0-based indexes or −1 for “not found.” In DB2, 0 means not found and 1 means the first character. Test with > 0 when you mean “contains.”

1
2
3
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE LOCATE('SON', LASTNAME) > 0;

POSITION

POSITION is the SQL-standard spelling of the same search. Typical form:

1
POSITION(search-string IN source-string [USING CODEUNITS16 | CODEUNITS32 | OCTETS])

IBM documents that LOCATE without start is similar to POSITION with the same string units, and LOCATE with start is similar to POSITION on SUBSTRING(source FROM start). Use POSITION when you want standard SQL. Use LOCATE when you need the optional start argument in one function call — that is the everyday z/OS pattern.

Related names you will see in manuals: POSSTR and STRPOS (search in a slightly different argument order / synonym family), and INSTR / LOCATE_IN_STRING for Oracle-style or extra occurrence arguments. Stick to LOCATE and POSITION in new Db2 for z/OS code unless a portability guide at your shop says otherwise.

LENGTH

LENGTH returns the length of a value. The argument can be any built-in type except XML. The result is a large integer and can be null.

1
LENGTH(expression)

For strings:

• The length is in bytes (string length as stored).
• Blanks count. CHAR(10) value ABC has length 10, not 3.
• VARCHAR uses the actual length, not the maximum. VARCHAR(12) holding ETHEL has length 5.
• The null indicator byte of a nullable column is not included.

1
2
3
4
5
6
7
8
SELECT LENGTH(FIRSTNME)
  FROM DSN8C10.EMP
 WHERE EMPNO = '000280';   -- 5 when FIRSTNME is ETHEL
 
SELECT LENGTH(HIREDATE) AS RAW_DATE,          -- 4
       LENGTH(CHAR(HIREDATE, EUR)) AS CHAR10  -- 10
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';

Beginners often write LENGTH(DATECOL) expecting 10. Internal DATE is 4 bytes. Format it with CHAR or VARCHAR first if you want the display length.

OCTET_LENGTH

OCTET_LENGTH returns the length of a string in octets (bytes). Schema SYSIBM. Use it when you mean “how much storage / how many bytes” and you want the standard function name. For Unicode data, byte length and character length diverge; OCTET_LENGTH stays on the byte side. CHARACTER_LENGTH with CODEUNITS32 answers “how many characters.”

1
2
3
4
SELECT LENGTH(FIRSTNME)       AS LEN_BYTES,
       OCTET_LENGTH(FIRSTNME) AS OCTETS
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';

On simple EBCDIC SBCS names, LENGTH and OCTET_LENGTH match. On UTF-8, a name with ü has more octets than CODEUNITS32 characters. Buffer sizes in COBOL PIC X(n) and VARCHAR host variables care about octets.

LEFT (STRLEFT) and RIGHT (STRRIGHT)

LEFT returns the leftmost units of a string. RIGHT returns the rightmost units. STRLEFT is a synonym for LEFT; STRRIGHT is a synonym for RIGHT (function-level compatibility).

1
2
LEFT(string-expression, integer [, CODEUNITS16 | CODEUNITS32 | OCTETS])
RIGHT(string-expression, integer [, CODEUNITS16 | CODEUNITS32 | OCTETS])

• integer — how many units to keep. Must be between 0 and the length attribute of the string in the chosen units.
• CODEUNITS16 — UTF-16 code units.
• CODEUNITS32 — UTF-32 characters (usually what you mean by “letters”).
• OCTETS — bytes. Not for graphic strings. CODEUNITS* not for bit data or binary strings.

The result is a varying-length string with the same length attribute as the source. IBM’s Jürgen example:

1
2
3
4
5
6
7
-- FIRSTNME / literal 'Jürgen' in UTF-8
LEFT(FIRSTNME, 2, CODEUNITS32)  -- 'Jü'
LEFT(FIRSTNME, 2, CODEUNITS16)  -- 'Jü'
LEFT(FIRSTNME, 2, OCTETS)       -- truncated ('J' plus blank)
 
RIGHT(FIRSTNME, 5, CODEUNITS32) -- 'ürgen'
RIGHT(FIRSTNME, 5, OCTETS)      -- truncated byte slice

LEFT(s, n) is the Unicode-aware cousin of SUBSTR(s, 1, n). RIGHT(s, n) is harder to fake with SUBSTR because you need LENGTH (in the same units) first. Prefer RIGHT instead of computing start = LENGTH − n + 1, especially on UTF-8.

1
2
3
SELECT LEFT(EMPNO, 3)  AS PREFIX,   -- department-style prefix if EMPNO layout agrees
       RIGHT(EMPNO, 3) AS SUFFIX
  FROM DSN8C10.EMP;

String units recap

Several functions on this page share the same unit keywords:

• OCTETS — bytes. Use for buffer sizes and EBCDIC byte layouts.
• CODEUNITS16 — UTF-16. Supplementary characters are two units.
• CODEUNITS32 — UTF-32. One unit per Unicode character, including supplementary characters.

Mixing units in one expression (LENGTH in bytes, LEFT in CODEUNITS32) is a common bug. Pick one unit system for a given column’s encoding and stay with it.

Finding, then slicing

A typical parse: find a separator, then take the left or right piece.

1
2
3
4
5
SELECT LASTNAME,
       LOCATE(',', LASTNAME) AS COMMA_AT,
       LEFT(LASTNAME, LOCATE(',', LASTNAME) - 1) AS BEFORE_COMMA
  FROM SOME_TABLE
 WHERE LOCATE(',', LASTNAME) > 0;

Guard the LOCATE > 0 test. LEFT(..., −1) from a missing comma is not a valid length. In COBOL you would do the same with two statements and a host variable for the position.

Explain It Like I'm Five

LENGTH is a ruler: it tells you how long the sticker is. On some stickers the ruler counts centimetres of paper (bytes). A fancy letter can be wider than one centimetre, so the ruler and a person counting letters can disagree — that is OCTETS versus CODEUNITS32. LOCATE is peeking along the sticker for a tiny word and shouting the tile number where it starts, starting at one, or shouting zero if it is not there. LEFT keeps the beginning of the sticker; RIGHT keeps the end.

Exercises

1. Write LOCATE to test whether LASTNAME contains the letters SON, and filter those rows.
2. Explain why LENGTH of a DATE is 4 and how to get the length of the ISO display form.
3. Compare LENGTH and OCTET_LENGTH on an EBCDIC CHAR column versus a Unicode VARCHAR that can hold ü.
4. Rewrite SUBSTR(NAME, 1, 4) as LEFT. Then write RIGHT to return the last four characters without computing LENGTH yourself.
5. Predict LOCATE('', 'HELLO') and LOCATE('Z', 'HELLO').