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 ( '[email protected]', instr('[email protected]','@',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 ( 'https://www.mandsconsulting.com/oracle', 8, instr ( 'https://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.
5 Comments on “Oracle SUBSTR with INTSR Function – SQL Syntax Examples”
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
Could you pls state what the result or outcome will be for the codes?
I wish to understand the concept behind this and why use +1 :
SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)) LIKE ‘Mc%’;
I’m trying to extract a value from some xml data in a CLOB column but have tried the EXTRACT command with no fortune. Is there a way to use these two string functions to do the extraction of a value between two tag values ? Thanks.
Dale
@ola orish
In this query ,after instr(cust_name ,)if your specified as null ‘ ‘ u won’t get any result .bcoz null value can’t add with integer .thank you very much for wrote this query