This discussion is archived
9 Replies Latest reply: Nov 21, 2012 2:42 AM by Manik RSS

Need to fetch Substring from a column

User_OIM Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Have you tried what i posted.it's simple..
  • 7. Re: Need to fetch Substring from a column
    jeneesh Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    This should give an idea. Select rows which has CHK > 0.
    SELECT str, INSTR (str, 'TEST') CHK
      FROM <resultset>;
    Cheers,
    Manik.

Legend

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