Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are some examples uses that will exemplify how to take advantage of their combined power.

Top Level Domain (TLD) From An Email Address

1
2
3
4
5
6
7
8

SELECT 
substr
(
  'xyz@mandsconsulting.com', 
  instr('xyz@mandsconsulting.com','@',1)+1
) 
FROM dual
--returns: mandsconsulting.com

Base URL

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT 
substr
(
  'http://www.mandsconsulting.com/oracle', 
  8, 
  instr
  (
    'http://www.mandsconsulting.com/oracle',
    '.com',
    1
  )+4-8
) 
FROM dual
--returns: www.mandsconsulting.com

Notes:

• This is a simple example where some assumptions are made. I used a starting position for the SUBSTR as 8 since I assumed here that the URL starts with “https://”. I also assumed that the end of the base URL ends with “.com”
• You will notice I end the substring with some arithmetic, adding 4 (the number of characters in “.com”) and subtracting 8 (the number of characters in “https://”)

If you are confused or need more explanation, feel free to leave a comment. Also, let us know if you have related challenges you are trying to figure out or requests for examples you would like to see added.

Related Information:

1. Oracle SUBSTR Function - SQL Syntax Examples

   The Oracle SUBSTR SQL Function is very common, and many times misused. Below shows the SUBSTR function along with the...

2. Oracle INSTR Function - SQL Syntax Examples

   The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming...

3. Oracle TO_DATE Function - SQL Syntax Examples

   The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle...

4. Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE)

   This article provides common examples of TO_CHAR using dates....

5. SQL LIKE - Oracle LIKE Operator - SQL Syntax Examples

   The reserved word LIKE in Oracle SQL represents an operator that performs some basic matching on text. LIKE provides a...

6. XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX

   Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result...

7. Oracle SQL Subselect Statements

   It is becoming more common that I find myself surprised by self-proclaimed, seasoned SQL developers who do not even understand...