8 Replies Latest reply: Jan 23, 2013 5:50 AM by BluShadow RSS

    instr()

    899401
      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
          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
            not getting

            thanks
            • 3. Re: instr()
              Frank Kulash
              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
                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
                  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
                    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
                      thanks
                      • 8. Re: instr()
                        BluShadow
                        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.