Oracle SUBSTR with INTSR Function – SQL Syntax Examples

Posted on May 28, 2009 in Technical Tips | 2 comments

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...

2. Oracle INSTR Function – SQL Syntax Examples

   The Oracle INSTR SQL function...

3. Oracle TO_DATE Function – SQL Syntax Examples

   The Oracle TO_DATE SQL function...

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

   This article provides common examples...

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

   The reserved word LIKE in...

6. First and Last Day of Month – Oracle SQL Syntax Examples

   First Day of Current Month...

7. Oracle Rename Table – Example Syntax and Dependencies

   Renaming a table in Oracle...

8. alter table, add column – Oracle Example Syntax

   Following is simple example syntax...