How to Extract a Substring From a String in Oracle or SQLite

Problem

You have a column of strings, and you'd like to get substrings from them.

Example 1

In the emails table, there is an email column. You'd like to display the first seven characters of each email.

The table looks like this:

Solution 1

SELECT
  email,
  SUBSTR(email, 1, 7) AS substring
FROM emails;

The result is:

Discussion

Use a SUBSTR() function. The first argument is the string or the column name. The second argument is the index of the character at which the substring should begin. The third argument is the length of the substring.

Watch out! Unlike in some other programming languages, the indexes start at 1, not 0. This means the first character has index 1, the second character has index 2, etc.

SUBSTR(email, 1, 7)will return the substrings of the values in the email column that start at the first character and go for seven characters.

Example 2

You'd like to display the substring between indexes 2 and 6 (inclusive).

Solution 2

SELECT
  email,
  SUBSTR(email, 2, 5) AS substring
FROM emails;

The result is:

Discussion

You use the SUBSTR() function just as in the previous example. This time, the second argument of the function is 2, since we want to start at index 2. The length of the substring is 5 (end_index - start_index + 1).

Example 3

You'd like to display the substring that starts at the @ sign and ends at the end of the string, but you don't know the exact indexes or lengths.

Solution 3

SELECT
  email,
  SUBSTR(email, INSTR(email, '@'), LENGTH(email) - INSTR(email, '@') + 1) AS substring
FROM emails;

The result is:

Discussion

You use the SUBSTR() function just as in the previous examples. This time, you're looking for a specific character whose position can vary from row to row. To find the index of the specific character, you can use the INSTR(column, character) function, where column is the literal string or the column from which you'd like to retrieve the substring, and character is the character at which you'd like to start the substring (here, @).

The third argument of the SUBSTR() function is the length of the substring. You can calculate it using the INSTR() and the LENGTH() functions. You do this by subtracting the index from the column length then adding 1:

LENGTH(email) - INSTR(email, '@') + 1

You may also want to retrieve a substring that doesn't end at the end of the string but at some specific character, e.g., before '.'. Here's how you can do this:

SELECT
  email,
  SUBSTR(email, INSTR(email, '@'), INSTR(email, '.') - INSTR(email, '@')) AS substring
FROM emails;

The result of this query is:

The part INSTR(email, '.') - INSTR(email, '@') simply calculates the length of the substring.