This discussion is archived
5 Replies Latest reply: Nov 26, 2012 12:33 AM by 311152 RSS

Query to show the output using SUBSTR or REGEXP_SUBSTR

311152 Newbie
Currently Being Moderated
Hi All,

I need a help..
I want to populate the data from varchar2 column, if it has the value 1,2,3 stored in it.
Then I need to pick the values between the commas and show some string for each value available with commas.


For Example: for '1' I should show India.
For '2' I should show US.. etc.

Regards
Suresh
  • 1. Re: Query to show the output using SUBSTR or REGEXP_SUBSTR
    myOra_help Journeyer
    Currently Being Moderated
    somthing like this...
    WITH t AS (SELECT '1,2,3' "STR" FROM dual) ,
    t1 AS (SELECT  SUBSTR(str,1,1)   "STR"  FROM t  UNION ALL 
                           SELECT  SUBSTR(str,3,1)   "STR"  FROM t  UNION ALL
                      SELECT  SUBSTR(str,5,1)   "STR"  FROM t    
                          )
    SELECT str ,DECODE(str,1,'India',2,'US',3,'other','defualt') "CNTRY" FROM t1;
    
    Output
    STR     CNTRY
    1     India
    2     US
    3     Other
  • 2. Re: Query to show the output using SUBSTR or REGEXP_SUBSTR
    Ashu_Neo Pro
    Currently Being Moderated
    Check this
    WITH data1(code) AS
    ( SELECT '1,2,3' FROM dual)
    SELECT 
    CASE (regexp_substr(code,'[^,][\d]*',1,level))
     WHEN '1' THEN 'India'
     WHEN '2' THEN 'Us'
     WHEN '3' THEN 'Japan'
     ELSE 'Others'
     END states
    FROM data1
    CONNECT BY  level <= regexp_count(code,',') + 1
    /
    
    O/P
    ------
    STATES
    ------
    India
    Us
    Japan
    Thanks!
  • 3. Re: Query to show the output using SUBSTR or REGEXP_SUBSTR
    ranit B Expert
    Currently Being Moderated
    GSKumar wrote:
    Hi All,

    I need a help..
    I want to populate the data from varchar2 column, if it has the value 1,2,3 stored in it.
    Then I need to pick the values between the commas and show some string for each value available with commas.


    For Example: for '1' I should show India.
    For '2' I should show US.. etc.

    Regards
    Suresh
    Use DECODE
    DECODE(<data>,1,'India',2,'US',3,'Rest of the World')
    Please post some input data and help us to help you.

    Ranit B.
  • 4. Re: Query to show the output using SUBSTR or REGEXP_SUBSTR
    Manik Expert
    Currently Being Moderated
    According to me, your algorithm should be as follows:

    1) Create table dfntn which defines the relation between id and nm
    e.g
    id    nm
    -----------
    1     India
    2     US
    3     Other
    2) Develop generic code which splits the data of any given values..
    e.g. 1,2,3 ...

    changed to
    id
    --
    1
    2
    3
    .
    .
    .
    Now join this id with the dfntn. This way u will be maintaining more robust code.. i.e. even though your id assignment changes, u can control that in the table level itself.

    Cheers,
    Manik.
  • 5. Re: Query to show the output using SUBSTR or REGEXP_SUBSTR
    311152 Newbie
    Currently Being Moderated
    It was really helpful.

Legend

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