How to Extract a Substring From a String in Oracle or SQLite Database: Oracle SQLite Operators: SUBSTR INSTR LENGTH Table of Contents Problem: Example 1: Solution 1: Discussion: Example 2: Solution 2: Discussion: Example 3: Solution 3: Discussion: 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: email jake99@gmail.com tamarablack@zoho.com notine@yahoo.fr jessica1995@onet.pl Solution 1: SELECT email, SUBSTR(email, 1, 7) AS substring FROM emails; The result is: emailsubstring jake99@gmail.comjake99@ tamarablack@zoho.comtamarab notine@yahoo.frnotine@ jessica1995@onet.pljessica 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: emailsubstring jake99@gmail.comake99 tamarablack@zoho.comamara notine@yahoo.frotine jessica1995@onet.plessic 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: emailsubstring jake99@gmail.com@gmail.com tamarablack@zoho.com@zoho.com notine@yahoo.fr@yahoo.fr jessica1995@onet.pl@onet.pl 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: emailsubstring jake99@gmail.com@gmail tamarablack@zoho.com@zoho notine@yahoo.fr@yahoo jessica1995@onet.pl@onet The part INSTR(email, '.') - INSTR(email, '@') simply calculates the length of the substring. Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: SQL for Data Analysis Cheat Sheet Where to Practice SQL Data Types in SQL How to Remove Junk Characters in SQL How to Recognize SQL Text Data Type SQL String Functions: A Complete Overview See also: How to Concatenate Strings in SQL How to Change Text to Lowercase in SQL How to Convert a String to Uppercase in SQL How to Replace Part of a String in SQL How to Trim Strings in SQL Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query