This discussion is archived
8 Replies Latest reply: Jan 23, 2013 3:50 AM by BluShadow RSS

instr()

899401 Newbie
Currently Being Moderated
Hi,

can someone explain me why 2nd statement has output
16
select instr('welcome to oracle','l') from dual
output
3
select instr('welcome to oracle','l',1,2) from dual
output
16
thanks
  • 1. Re: instr()
    Purvesh K Guru
    Currently Being Moderated
    896398 wrote:
    Hi,

    can someone explain me why 2nd statement has output
    16
    select instr('welcome to oracle','l') from dual
    output
    3
    select instr('welcome to oracle','l',1,2) from dual
    output
    16
    thanks
    because 3rd parameter in INSTR specifies the Occurance of the Character(s) (provided as 1st parameter). Since you provided search character as 'l' and 2nd occurance, it provided you the position of 2nd 'l' in search string i.e. 16.

    Extract from Documentation:- Read Here for more information.
    INSTR (string , substring [, position [, occurrence]])
    
    
    Arguments
    
    string
    The text expression to search.
    
    substring
    The string to search for.
    
    position
    A nonzero INTEGER indicating where in string the function begins the search. INSTR calculates position using characters as defined by the input character set. INSTRB calculates position using bytes. INSTRC calculates position using Unicode complete characters. INSTR2 calculates position using UCS2 code points. INSTR4 calculates position using UCS4 code points.
    
    When position is negative, then INSTR counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the beginning of string.
    
    occurrence
    An INTEGER indicating which occurrence of string the function should search for. The value of occurrence must be positive. The default values of occurrence is 1, meaning the function searches for the first occurrence of substring.
    Edited by: Purvesh K on Jan 23, 2013 4:51 PM
  • 2. Re: instr()
    899401 Newbie
    Currently Being Moderated
    not getting

    thanks
  • 3. Re: instr()
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Whenever you have trouble understanding a function, start by reviewing what the SQL Language manual says about the function.

    Here's what you're asking for:
    SELECT  INSTR ( 'welcome to oracle'     -- In this string
               , 'l'                   -- where does 'l' occur
               , 1               -- counting from the 1st character
               , 2               -- the 2nd time
               ) 
    FROM    dual;
    The 1st 'l', as you found earlier, occurs at position 3.
    The 2nd 'l' is at position 16.

    If you still have questions, then post here, but make the question more specific so we can help you more.
    For example: "The manual says ... so I was expecting the output of ... to be ... because ... Why did I get 16 instead?"
  • 4. Re: instr()
    Purvesh K Guru
    Currently Being Moderated
    896398 wrote:
    not getting

    thanks
    Does it really appear to you that I am sitting with a Crystal ball in my lap and able to read your mind as to what you are not understanding?

    Let me explain you This way:
    INSTR (string , substring [, position [, occurrence]])
                 |        |            |            |
                 |        |            |            \/
                 |        |            \/          The Occurance of Substring that is being searched. i.e. If provided value as 2, it looks for Second Occurance of the substring in the String, if Found returns the position else returns 0.
                 |        \/       Position from where search should start
                 \/String That is to be searched
    The Input String.    
    When your Query goes this was:
    select instr('welcome to oracle','l',1,2) from dual
                        |             |  |  |
                        |             |  |  |
                        |             |  |  \/
                        |             |  \/ Check for 2nd Occurance of String ('l')
                        |             \/ Position from where search Begins. Here, First Character
                        \/  String that is to be searched
    Your Original String
    Are things more clear with this explanation?

    Edited by: Purvesh K on Jan 23, 2013 4:55 PM
  • 5. Re: instr()
    Rahul_India Journeyer
    Currently Being Moderated
    select instr('welcome to oracle','l',1,2) from dual

    You want to display the position of second occurence of " l " in the string
            
    Welcome to oracle 
    12345               16
    1 denotes the startin position of search.

    L occurs 2 times in the string .First time at the 3rd position and 2nd time at the 16th position.
    What you are asking to tell the position of second occurence of l.
    select instr('welcome to oraclel','l',4) from dual
    will give you output 16 only 
    because search will start from C so it will ignore the first occurence of L.
  • 6. Re: instr()
    AlbertoFaenza Expert
    Currently Being Moderated
    896398 wrote:
    Hi,

    can someone explain me why 2nd statement has output
    16
    select instr('welcome to oracle','l') from dual
    output
    3
    select instr('welcome to oracle','l',1,2) from dual
    output
    16
    thanks
    OMG!! Are you going to do this for all SQL functions?
    Have you tried to read and understand the documentation first?
    SUBSTR and INSTR are basic functions and are also well documented to my opinion.

    Regards.
    Al
  • 7. Re: instr()
    899401 Newbie
    Currently Being Moderated
    thanks
  • 8. Re: instr()
    BluShadow Guru Moderator
    Currently Being Moderated
    896398 wrote:
    Hi,

    can someone explain me why 2nd statement has output
    16
    select instr('welcome to oracle','l') from dual
    output
    3
    select instr('welcome to oracle','l',1,2) from dual
    output
    16
    thanks
    Please READ THE DOCUMENTATION.

    Here's a link, where you can pick your database version, and search for the function you want to understand.

    http://tahiti.oracle.com/

    The forums are NOT a place for you to keep posting to ask basic questions that are easily answered by reading the documentation, they are a place for people who are having an issue with their coding and have exhausted all the standard avenues, such as documentation, googling for the answer etc.

    If you post any further basic documentation questions, your questions will likely be locked or removed.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points