Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 38 ยท Functions and Expressions

String Functions in Oracle

Master Oracle string functions: UPPER, INITCAP, SUBSTR, INSTR, REPLACE, TRANSLATE, LPAD, TRIM, and the REGEXP family, with the 1-based positions Oracle uses.

Text is the messiest data in any database: names with extra spaces, codes that need padding, phone numbers in five formats. Oracle ships a large set of string functions for cleaning, searching and reshaping it. Almost all of them work on a single value at a time, so you can use them in SELECT, WHERE, ORDER BY and in PL/SQL code.

Each function below is shown with its Oracle spelling. Where SQLite has an equivalent, a runnable example follows; functions that only Oracle has (INITCAP, LPAD, TRANSLATE, the REGEXP_ family) are read-only, with the exact result when we are sure.

Case and length

Function Result for 'oRaCle db'
UPPER(s) ORACLE DB
LOWER(s) oracle db
INITCAP(s) Oracle Db (first letter of each word upper, rest lower)
LENGTH(s) 9

LENGTH counts characters; LENGTHB counts bytes. LENGTH of NULL (and of '', which is NULL in Oracle) is NULL.

SQL Case and length ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Case-insensitive search is the most common use: WHERE UPPER(last_name) = 'SMITH'. Because the function wraps the column, a normal index on last_name cannot be used; create a function-based index on UPPER(last_name) if the query is frequent.

SUBSTR and INSTR

SUBSTR(string, start, length) cuts a piece out. Positions are 1-based. A negative start counts from the end. Omit length to take everything to the end.

SQL
SUBSTR('Oracle Database', 1, 6)    -- 'Oracle'
SUBSTR('Oracle Database', 8)       -- 'Database'
SUBSTR('Oracle Database', -4)      -- 'base'

If start is 0, Oracle treats it as 1. INSTR(string, search, start, occurrence) returns the position of search, or 0 when it is absent:

SQL
INSTR('a-b-c', '-')        -- 2
INSTR('a-b-c', '-', 1, 2)  -- 4  (second occurrence)
INSTR('a-b-c', 'z')        -- 0

Together they split text. This runnable stand-in splits an email into user and domain (SQLite has the same substr and instr, with the same positions):

SQL Splitting with SUBSTR and INSTR ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

REPLACE, TRANSLATE and TRIM

REPLACE(s, search, replacement) swaps every occurrence of a substring. If you omit the replacement it deletes the text. TRANSLATE(s, from, to) replaces character by character: the first character of from becomes the first of to, and so on.

SQL
REPLACE('2024-05-17', '-', '/')            -- '2024/05/17'
TRANSLATE('4412-8899', '0123456789', 'abcdefghij')   -- 'eebc-iijj'
TRANSLATE('(555) 010-2030', '0123456789() -', '0123456789')  -- '5550102030'

The last example uses a common trick: characters that appear in from but have no partner in to are deleted, which is a one-line way to strip punctuation.

TRIM removes spaces (or another single character) from both ends:

SQL
TRIM('   hello  ')              -- 'hello'
TRIM(LEADING '0' FROM '000450') -- '450'
TRIM(BOTH 'x' FROM 'xxabcxx')   -- 'abc'
LTRIM('xxabc', 'x')             -- 'abc'    removes any of the listed characters on the left
RTRIM('abc   ')                 -- 'abc'
SQL REPLACE and TRIM ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

LPAD and RPAD

LPAD(s, n, pad) pads on the left until the result is n characters, and RPAD pads on the right. If s is already longer than n, it is cut to n.

SQL
LPAD('42', 6, '0')       -- '000042'
RPAD('Name', 10, '.')    -- 'Name......'
LPAD('abcdefgh', 4)      -- 'abcd'   (truncates!)

They are the standard tools for fixed-width reports and zero-padded codes. Stand-in for LPAD('42', 6, '0') in SQLite:

SQL Stand-in for LPAD

Press Run (or Ctrl + Enter) to see the result here.

Concatenation: || and CONCAT

|| joins any number of strings. CONCAT(a, b) takes exactly two. Oracle converts numbers and dates to text automatically, but it is safer to convert explicitly with TO_CHAR (see conversion functions).

SQL
SELECT first_name || ' ' || last_name AS full_name FROM employees;

For lists of rows combined into one string, use LISTAGG, covered in aggregate and analytic functions.

Regular expressions

Oracle's regex functions use POSIX-style patterns (with \d, \s, \w, {n,m}, lazy *? and so on):

Function Purpose
REGEXP_LIKE(s, pattern [, flags]) Condition: does the string match?
REGEXP_SUBSTR(s, pattern [, pos, occ, flags, group]) Extract a match (or one captured group)
REGEXP_REPLACE(s, pattern, repl [, pos, occ, flags]) Replace matches; \1 refers to group 1
REGEXP_INSTR(s, pattern ...) Position of a match
REGEXP_COUNT(s, pattern) Number of matches

Flag 'i' means case-insensitive, 'c' case-sensitive, 'm' multi-line.

SQL
SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[a-z.]+@example\.com$');

SELECT REGEXP_SUBSTR('Order A-1234 shipped', '[A-Z]-\d+') FROM dual;      -- 'A-1234'
SELECT REGEXP_REPLACE('555.010.2030', '(\d{3})\.(\d{3})\.(\d{4})', '(\1) \2-\3') FROM dual;  -- '(555) 010-2030'
SELECT REGEXP_COUNT('banana', 'a') FROM dual;                              -- 3

Regular expressions are powerful but slower than SUBSTR, INSTR and LIKE. Use the simple functions when they are enough.

NULL handling and a few extras

Most string functions return NULL when an input is NULL; the exception is ||, which treats NULL as empty. Other handy functions:

  • ASCII('A') is 65 and CHR(65) is 'A'.
  • SOUNDEX('Smith') and SOUNDEX('Smyth') give the same code, which helps with "sounds like" searches.
  • REVERSE exists but is undocumented; do not rely on it. Reverse a string with REGEXP_REPLACE/PL/SQL loops, or in SQL with LISTAGG over SUBSTR by position.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does SUBSTR('Oracle', -3) return?

    A negative start counts from the end of the string, so -3 starts at the third character from the right: 'cle'.
  2. 2. What does LPAD('7', 3, '0') return?

    LPAD pads on the left up to a total length of 3 characters.
  3. 3. What does INSTR('a-b-c', '-', 1, 2) return?

    The second occurrence of '-' is at position 4 (1-based).
  4. 4. Which function replaces characters one-for-one and deletes characters that have no partner?

    TRANSLATE maps each character in the 'from' list to the character at the same position in 'to', and removes characters with no counterpart.
  5. 5. Why is WHERE UPPER(last_name) = 'SMITH' slow on a large table?

    A normal index stores the raw values. A function-based index on UPPER(last_name) solves it.

Summary

  • UPPER, LOWER, INITCAP and LENGTH handle case and size; positions in SUBSTR and INSTR start at 1.
  • REPLACE swaps substrings, TRANSLATE swaps characters, TRIM/LTRIM/RTRIM strip edges.
  • LPAD and RPAD pad (and truncate) to a fixed width.
  • || concatenates and treats NULL as empty; most other functions return NULL for NULL.
  • The REGEXP_ functions handle pattern matching, extraction and replacement.

Next: Number and Date Functions