Oracle SUBSTR with INTSR Function – SQL Syntax Examples
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:
- Oracle SUBSTR Function – SQL Syntax Examples
The Oracle SUBSTR SQL Function...
- Oracle INSTR Function – SQL Syntax Examples
The Oracle INSTR SQL function...
- Oracle TO_DATE Function – SQL Syntax Examples
The Oracle TO_DATE SQL function...
- Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)
This article provides common examples...
- SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples
The reserved word LIKE in...
- First and Last Day of Month – Oracle SQL Syntax Examples
First Day of Current Month...
- Oracle Rename Table – Example Syntax and Dependencies
Renaming a table in Oracle...
- alter table, add column – Oracle Example Syntax
Following is simple example syntax...

Please give some more explanation on example Base URL also give some challenging example on INSTR and SUBSTR.
Hello Team,
I have not understood substr with instr function.Need more explanation.
Thanks & regards,
Souvik Ghosh