This discussion is archived
6 Replies Latest reply: Jan 21, 2013 2:34 AM by Karthick_Arp RSS

Using SUBSTR

Smile Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    Use Union operator
  • 2. Re: Using SUBSTR
    NSK2KSN Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank You for the reply,
    Is that possible using REGEXP_SUBSTR
    I tried but ended unsuccessful
  • 5. Re: Using SUBSTR
    jeneesh Guru
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    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> 

Legend

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