Oracle SUBSTR with INTSR Function – SQL Syntax Examples

AshokTechnical Tips5 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
(
  '[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”

  1. Please give some more explanation on example Base URL also give some challenging example on INSTR and SUBSTR.

  2. Hello Team,

    I have not understood substr with instr function.Need more explanation.

    Thanks & regards,

    Souvik Ghosh

  3. 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%’;

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *