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. Google Apps Reporting API - Not quite this easy with Exchange I have been somewhat enamored by Google Apps and its potential. Many enterprises have been happy with Exchange for mail,...