Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: INSTR and SUBSTR

    Hi

    Could someone please help me understand the below SQL? I've had a look on the net and docs but am still not clear.

    select SUBSTR('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU',INSTR('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU',':')+1,
    length('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU')-INSTR('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU',':')) from dual

    Thanks
    Shajju

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SUBSTR is a function returns a substring of a string. Its parameters are
    • input string
    • starting position
    • length of a substring
    For example, SUBSTR('abcd', 2, 1) will return 'b': 'abcd' is the input string. Start from position 2, and take a substring whose length is 1. That would be 'b'. Right?

    Now, your query does the same only if you'd rewrite it to a simpler form, such as
    Code:
    SQL> select
      2    substr(
      3           'abc:def',
      4           instr('abc:def', ':') + 1,
      5           length('abc:def') - instr('abc:def', ':')
      6          )
      7  from dual;
    
    SUB
    ---
    def
    
    SQL>
    First parameter is our input string: 'abc:def'

    The second one is starting position. Here we have to calculate it; author of the query wanted to find the first occurrence of the colon (:) sign. To do that, we have used the INSTR function. "+1" means "start from the position of the colon sign plus one more character". In our example, it is the character 'd' or, in numbers, 4 + 1 = 5.

    Finally, the length: in this example, we want to take all characters to the end of the input string. It is "length of the whole input string MINUS position of the colon sign", which is then 7 - 4 = 3.

    Having that in mind, this query could have been rewritten as
    Code:
    SQL> select
      2    substr('abc:def', 5, 3)
      3  from dual;
    
    SUB
    ---
    def
    
    SQL>
    The same goes for your query.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    very nice explanation littlefoot!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    It helps to deconstruct these things, to find out what's going on

    Code:
    select INSTR('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU',':')+1 as val1,
           length('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU')  as val2,
           INSTR('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU',':') as val3,
           length('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU')-INSTR('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU',':') as val4
    from dual
    
    
          VAL1       VAL2       VAL3       VAL4
    ---------- ---------- ---------- ----------
            30         42         29         13
    1 row selected.
    Then simplify

    Code:
    select SUBSTR('Ghala-HLR1V9/CPU Measurement:MN=231,MT=DSU',30,13) as val from dual;
    
    
    VAL          
    -------------
    MN=231,MT=DSU
    1 row selected.
    --=Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •