5 Replies Latest reply: Nov 26, 2012 2:33 AM by 311152 RSS

    Query to show the output using SUBSTR or REGEXP_SUBSTR

    311152
      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
          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
            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
              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
                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
                  It was really helpful.