6 Replies Latest reply: Jan 21, 2013 4:34 AM by _Karthick_ RSS

    Using SUBSTR

    Smile
      Hi I've the following string
      1234*abc_cd*12_4s9780*
      I have to divide the above string into 3 sub string having the pattern *
      So i used the following 3 queries
      SELECT   SUBSTR('1234*abc_cd*12_4s9780*',0,INSTR('1234*abc_cd*12_4s9780*','*',1)-1) str_divide
      FROM dual
      
      SELECT SUBSTR('1234*abc_cd*12_4s9780*',INSTR('1234*abc_cd*12_4s9780*','*',1,1)+1,
               INSTR('1234*abc_cd*12_4s9780*','*',1,2)-INSTR('1234*abc_cd*12_4s9780|','*',1,1)-1) str_divide
      FROM DUAL
      
      SELECT SUBSTR('1234*abc_cd*12_4s9780*',INSTR('1234*abc_cd*12_4s9780*','*',1,2)+1,INSTR('1234*abc_cd*12_4s9780*','*',1,3)-
      INSTR('1234*abc_cd*12_4s9780*','*',1,2)-1) str_divide
      FROM DUAL 
      Is there any other way to divide the strings using single query , instead of using 3 queries using substr,instr

      Thanks

      Edited by: Smile on Jan 21, 2013 5:05 AM
        • 1. Re: Using SUBSTR
          Rahul_India
          Use Union operator
          • 2. Re: Using SUBSTR
            NSK2KSN
            1) post your database version; use select * from v$version;
            2) if your expected output is not '12_4s9780' provide the same for the given input string
            • 3. Re: Using SUBSTR
              jeneesh
              If you need it as 3 columns, you could just do as
              SELECT   SUBSTR('1234*abc_cd*12_4s9780*',0,INSTR('1234*abc_cd*12_4s9780*','*',1)-1) str_divide,
                    SUBSTR('1234*abc_cd*12_4s9780*',INSTR('1234*abc_cd*12_4s9780*','*',1,1)+1,
                        INSTR('1234*abc_cd*12_4s9780*','*',1,2)-INSTR('1234*abc_cd*12_4s9780|','*',1,1)-1) str_divide2,
                      SUBSTR('1234*abc_cd*12_4s9780*',INSTR('1234*abc_cd*12_4s9780*','*',1,2)+1,INSTR('1234*abc_cd*12_4s9780*','*',1,3)-
                    INSTR('1234*abc_cd*12_4s9780*','*',1,2)-1) str_divide3
              FROM dual
              If you need three rows, use UNION
              • 4. Re: Using SUBSTR
                Smile
                Thank You for the reply,
                Is that possible using REGEXP_SUBSTR
                I tried but ended unsuccessful
                • 5. Re: Using SUBSTR
                  jeneesh
                  Smile wrote:
                  Thank You for the reply,
                  Is that possible using REGEXP_SUBSTR
                  I tried but ended unsuccessful
                  select regexp_substr('1234*abc_cd*12_4s9780*','[^*]+',1,1) s1,
                    regexp_substr('1234*abc_cd*12_4s9780*','[^*]+',1,2) s2,
                    regexp_substr('1234*abc_cd*12_4s9780*','[^*]+',1,3) s13
                  from dual;
                  • 6. Re: Using SUBSTR
                    _Karthick_
                    If you want them in 3 rows then
                    SQL> select regexp_substr(str, '[^*]+', 1, level) str
                      2    from (
                      3            select '1234*abc_cd*12_4s9780*' str from dual
                      4         )
                      5 connect
                      6      by level <= length(str) - length(replace(str, '*'))
                      7  /
                     
                    STR
                    ----------------------
                    1234
                    abc_cd
                    12_4s9780
                     
                    SQL>