Explain the SUBSTR and INSTR string functions in PL/SQL

How it works

SUBSTR(string, start_position, [length]) extracts a substring — a negative start_position counts backward from the end of the string, and omitting length returns everything through the end. INSTR(string, substring, [start_position], [occurrence]) returns the character position where a substring is found (0 if it isn't). Combining the two — using INSTR's result as SUBSTR's length — is the standard pattern for pulling a token out from before or after a delimiter.

SELECT
  SUBSTR('john.doe@example.com', 1, 4)                                       AS first_four,
  SUBSTR('john.doe@example.com', -11)                                        AS from_the_end,
  INSTR('john.doe@example.com', '@')                                         AS at_position,
  SUBSTR('john.doe@example.com', 1, INSTR('john.doe@example.com', '@') - 1)  AS username
FROM dual;
-- username => 'john.doe'

Related Questions