9 Replies Latest reply: Nov 21, 2012 4:42 AM by Manik RSS

    Need to fetch Substring from a column

    User_OIM
      Hi,
      I have a column in my database called MYTABLE_VALUE in the MYTABLE table. Below is the sample data which it contains in each row.
      I need to fetch the value between <UserLogin> and </UserLogin> which in the below case is "TEST". Can someone guide me as to how i can fetch this data for all rows.


      +<Data><Users><User><UserLogin>TEST</UserLogin><FirstName>test</FirstName><LastName>one</LastName></User></Users><Resources><Resource key="99"><ResourceName>SAP</ResourceName></Resource></Resources></Data>+


      Thank you
        • 1. Re: Need to fetch Substring from a column
          Frank Kulash
          Hi,

          In any version of Oracle, you can use SUBSTR and INSTR:
          WITH     got_pos          AS
          (
               SELECT     txt
               ,     INSTR (txt, '<UserLogin>') + 11     AS start_pos     -- 11 = LENGTH ('<UserLogin>')
               ,     INSTR (txt, '</UserLogin>')       AS end_pos
               FROM     table_x
          )
          SELECT     SUBSTR ( txt
                      , start_pos
                      , end_pos - start_pos
                      )       AS user_login
          FROM    got_pos
          ;
          What results do you want if both tags do not appear in order, or if either occurs more than once?

          Starting in Oracle 10, you can also use regular expressions, but they are less efficient, and, without the Oracle 11 enhancements to REGEXP_SUBSTR, not really simpler.
          • 2. Re: Need to fetch Substring from a column
            971895
            Try like..

            select extractvalue(a,'/Data/Users/User/UserLogin') from
            (select xmltype('<Data><Users><User><UserLogin>TEST</UserLogin><FirstName>test</FirstName><LastName>one</LastName></User></Users><Resources><Resource key="99"><ResourceName>SAP</ResourceName></Resource></Resources></Data>') a
            from dual) t
            • 3. Re: Need to fetch Substring from a column
              Manik
              Check this:
              SELECT SUBSTR (
                        SUBSTR (SUBSTR (str, INSTR (str, 'UserLogin>')),
                                INSTR (SUBSTR (str, INSTR (str, 'UserLogin>')), '>') + 1),
                        1,
                        INSTR (
                           SUBSTR (
                              SUBSTR (str, INSTR (str, 'UserLogin>')),
                              INSTR (SUBSTR (str, INSTR (str, 'UserLogin>')), '>') + 1),
                           '<')
                        - 1)
                FROM t;
              Cheers,
              Manik
              • 4. Re: Need to fetch Substring from a column
                Purvesh K
                One way using Regular Expression:-
                with data as
                (
                  select '<Data><Users><User><UserLogin>*TEST*</UserLogin><FirstName>test</FirstName><LastName>one</LastName></User></Users><Resources><Resource key="99"><ResourceName>SAP</ResourceName></Resource></Resources></Data>
                ' col from dual
                )
                select replace(regexp_substr(col, '<UserLogin>[^<]+', 1), '<UserLogin>') userlogin
                  from data;
                
                USERLOGIN 
                --------- 
                *TEST*    
                Using SUBSTR and INSTR
                with data as
                (
                  select '<Data><Users><User><UserLogin>*TEST*</UserLogin><FirstName>test</FirstName><LastName>one</LastName></User></Users><Resources><Resource key="99"><ResourceName>SAP</ResourceName></Resource></Resources></Data>
                ' col from dual
                )
                select substr(col, instr(col, '<UserLogin>', 1) + length('<UserLogin>'), (instr(col, '</UserLogin>', 1) - 1 - (instr(col, '<UserLogin>', 1) + length('<UserLogin>') - 1))) userlogin
                  from data;
                
                USERLOGIN 
                --------- 
                *TEST*   
                • 5. Re: Need to fetch Substring from a column
                  User_OIM
                  One more thing Manik, How do I search a string say "TEST" in all the rows given by the below query? Might sound like a noob question to you, but I am one.

                  SELECT SUBSTR (
                  SUBSTR (SUBSTR (str, INSTR (str, 'UserLogin>')),
                  INSTR (SUBSTR (str, INSTR (str, 'UserLogin>')), '>') + 1),
                  1,
                  INSTR (
                  SUBSTR (
                  SUBSTR (str, INSTR (str, 'UserLogin>')),
                  INSTR (SUBSTR (str, INSTR (str, 'UserLogin>')), '>') + 1),
                  '<')
                  - 1)
                  FROM t;
                  • 6. Re: Need to fetch Substring from a column
                    971895
                    Have you tried what i posted.it's simple..
                    • 7. Re: Need to fetch Substring from a column
                      jeneesh
                      select extractvalue(col,'/Data/Users/User/UserLogin') col
                      from 
                        (
                          select xmltype(COL) col
                          from your_table
                        )
                      where extractvalue(col,'/Data/Users/User/UserLogin') = '*TEST*'  ;
                      • 8. Re: Need to fetch Substring from a column
                        User_OIM
                        I am not able to get the desired result from your query. This is only extracting the value of UserLogin from the complete string in the row. I need to search all the Userlogins in the particular column. From Manik's query, i have managed to get all the userLogin values in the column. The sample result is;

                        TEST
                        TEST1
                        TEST2
                        TEST99

                        Now i need to search for TEST99 in this result. How do i do that? I want something like;

                        select * from (Result of Manik's query) where field="TEST99";

                        I am not sure what the value of "field" should be.
                        • 9. Re: Need to fetch Substring from a column
                          Manik
                          This should give an idea. Select rows which has CHK > 0.
                          SELECT str, INSTR (str, 'TEST') CHK
                            FROM <resultset>;
                          Cheers,
                          Manik.