1 2 Previous Next 24 Replies Latest reply: Sep 12, 2013 7:33 AM by Manik RSS

    How to fetch data using Substring & Instring

    ODI_NewUser

      Hi ,

       

      I have data like

       

      a_76488b_2780c

      a_76488b_2780c_

      a_76488b_c2780

      a_76488b_c2780

      a_31487b_5542

      a_76488b_2780

       

      i want to fetch data like last 4 numeric digit only

       

      2780

      2780

      2780

      2780

      5542

      2780

        • 1. Re: How to fetch data using Substring & Instring
          Ishan

          There is no pattern in the input that you have provided. What exactly is the logic that you are looking for.

           

            select substr('a_76488b_2780c_', instr('a_76488b_2780c_', '_', 3)+1, 4) from dual;

           

          But this will not give you the correct output for

          a_76488b_c2780 

          a_76488b_c2780

           

          Above query can be modified a bit to replace the "C" as well.

          • 2. Re: How to fetch data using Substring & Instring
            Manik

            One possible way: (based on your input)...

             

            WITH t AS

                    (SELECT 'a_76488b_2780c' str FROM DUAL

                     UNION ALL

                     SELECT 'a_76488b_2780c_' FROM DUAL

                     UNION ALL

                     SELECT 'a_76488b_c2780' FROM DUAL

                     UNION ALL

                     SELECT 'a_76488b_c2780' FROM DUAL

                     UNION ALL

                     SELECT 'a_31487b_5542' FROM DUAL

                     UNION ALL

                     SELECT 'a_76488b_2780' FROM DUAL)

            SELECT REGEXP_REPLACE (

                      SUBSTR (RTRIM (str, '_'), INSTR (RTRIM (str, '_'), '_', -1) + 1),

                      '[^[:digit:]]') str

              FROM t;

             

             

            Output:

             

            str

            ----

            2780

            2780

            2780

            2780

            5542

            2780

             

            Cheers,

            Manik.

            • 3. Re: How to fetch data using Substring & Instring
              Purvesh K

              One way of doig it:

               

              with data as

              (

                select 'a_76488b_2780c' col from dual union all

                select 'a_76488b_2780c_' col from dual union all

                select 'a_76488b_c2780' col from dual union all

                select 'a_76488b_c2780' col from dual union all

                select 'a_31487b_5542' col from dual union all

                select 'a_76488b_2780' col from dual

              )

              select col, regexp_substr(substr(col, instr(col, '_', 1, 2) + 1), '[[:digit:]]+') dig

                from data;

               

              COL             DIG            
              --------------- ---------------
              a_76488b_2780c  2780           
              a_76488b_2780c_ 2780           
              a_76488b_c2780  2780           
              a_76488b_c2780  2780           
              a_31487b_5542   5542           
              a_76488b_2780   2780   
                     

              6 rows selected

              • 4. Re: How to fetch data using Substring & Instring
                BluShadow

                Slightly simpler method:

                 

                SQL> ed
                Wrote file afiedt.buf

                  1  WITH t AS (SELECT 'a_76488b_2780c' str FROM DUAL UNION ALL
                  2             SELECT 'a_76488b_2780c_' FROM DUAL UNION ALL
                  3             SELECT 'a_76488b_c2780' FROM DUAL UNION ALL
                  4             SELECT 'a_76488b_c2780' FROM DUAL UNION ALL
                  5             SELECT 'a_31487b_5542' FROM DUAL UNION ALL
                  6             SELECT 'a_76488b_2780' FROM DUAL
                  7            )
                  8  --
                  9  select regexp_replace(str,'.*([0-9]{4})[^0-9]*$','\1') as str
                10* from t
                SQL> /

                 

                STR
                -------------------------------------------------------------------
                2780
                2780
                2780
                2780
                5542
                2780

                 

                6 rows selected.

                • 5. Re: How to fetch data using Substring & Instring
                  ODI_NewUser

                  Hi Manik Thanks for reply

                   

                   

                  but i am getting this data in a table column , and i don't want to use REGEXP_REPLACE, instead of i am going to use REPLACE

                   

                   

                  is it possible

                  • 6. Re: How to fetch data using Substring & Instring
                    BluShadow

                    ODI_NewUser wrote:

                     

                    Hi Manik Thanks for reply

                     

                     

                    but i am getting this data in a table column,

                    That's not a problem, you apply the same query to your own column of data.  We just use WITH clauses to simulate tables rather than create them on our own databases.

                     

                    and i don't want to use REGEXP_REPLACE, instead of i am going to use REPLACE

                     

                     

                    is it possible

                     

                    Probably, but as your data doesn't appear to have a consistent format (that you've told us of) it's easier to use regular expression to extract just the last 4 digits of the string regardless of where those last 4 digits are in the string.

                    If you can explain what different formats of string are explicitly going to exist in the data then it could be coded using SUSBTR and INSTR, or even other methods, but regexp makes it easy.

                    • 7. Re: How to fetch data using Substring & Instring
                      Manik

                      Without regexp??? That depends on your input.... if you know a definite character set you want to replace, then you may go for:

                       

                      (strictly dependant on your input given inthe question)

                       

                      SELECT TRANSLATE (

                                SUBSTR (RTRIM (str, '_'), INSTR (RTRIM (str, '_'), '_', -1) + 1),

                                '~c_',' ')

                                str

                        FROM t;

                       

                       

                      Cheers,

                      Manik.

                      • 8. Re: How to fetch data using Substring & Instring
                        BluShadow

                        For example, if you can say that the last 4 digits will only ever optionally have "_" and "c" after them you could do something as simple as:

                         

                        SQL> ed
                        Wrote file afiedt.buf

                          1  WITH t AS (SELECT 'a_76488b_2780c' str FROM DUAL UNION ALL
                          2             SELECT 'a_76488b_2780c_' FROM DUAL UNION ALL
                          3             SELECT 'a_76488b_c2780' FROM DUAL UNION ALL
                          4             SELECT 'a_76488b_c2780' FROM DUAL UNION ALL
                          5             SELECT 'a_31487b_5542' FROM DUAL UNION ALL
                          6             SELECT 'a_76488b_2780' FROM DUAL
                          7            )
                          8  --
                          9  select substr(rtrim(str,'_c'),-4) as str
                        10* from t
                        SQL> /

                         

                        STR
                        ----
                        2780
                        2780
                        2780
                        2780
                        5542
                        2780

                         

                        6 rows selected.

                        • 9. Re: How to fetch data using Substring & Instring
                          ODI_NewUser

                          Hi i am writing this one as

                           

                          SELECT REPLACE(SUBSTR (RTRIM (haskey, '_'), INSTR(RTRIM(haskey, '_'), '_', -1) + 1),'[^[:digit:]]')

                          FROM my_Table

                           

                          but i am getting below result

                           

                          2780c

                          2780c

                          c2780

                          c2780

                          5542

                          2780

                          • 10. Re: How to fetch data using Substring & Instring
                            bencol

                            I don't know what you have against rexexp_replace, but this works if you want all the digits after the second underscore:

                            WITH t AS

                                    (SELECT 'a_76488b_2780c' str FROM DUAL

                                     UNION ALL

                                     SELECT 'a_76488b_2780c_' FROM DUAL

                                     UNION ALL

                                     SELECT 'a_76488b_c2780' FROM DUAL

                                     UNION ALL

                                     SELECT 'a_76488b_c2780' FROM DUAL

                                     UNION ALL

                                     SELECT 'a_31487b_5542' FROM DUAL

                                     UNION ALL

                                     SELECT 'a_76488b_2780' FROM DUAL

                                    )

                            SELECT replace(after_second_und

                                          ,replace(translate(after_second_und

                                                            ,'0123456789'

                                                            ,'@'

                                                            )

                                                  ,'@'

                                                  ,NULL

                                                  )

                                          ,NULL

                                          ) your_number

                            FROM   (SELECT   SUBSTR(str,instr(str,'_',1,2)+1) after_second_und

                                    FROM t

                                   );

                             

                            YOUR_NUMBER

                            _______________

                            2780

                            2780

                            2780

                            2780

                            5542

                            2780

                            • 11. Re: How to fetch data using Substring & Instring
                              BluShadow

                              ODI_NewUser wrote:

                               

                              Hi i am writing this one as

                               

                              SELECT REPLACE(SUBSTR (RTRIM (haskey, '_'), INSTR(RTRIM(haskey, '_'), '_', -1) + 1),'[^[:digit:]]')

                              FROM my_Table

                               

                              but i am getting below result

                               

                              2780c

                              2780c

                              c2780

                              c2780

                              5542

                              2780

                               

                              Well, don't write it like that then, use one of the solutions that we've shown works.

                              • 12. Re: How to fetch data using Substring & Instring
                                chris227

                                Without regexp.

                                Assumption chr(0) never occurs in the string

                                 

                                with testdata as (

                                select 'a_76488b_2780' str from dual union all

                                 

                                select 'a_76488b_2780c' from dual union all

                                 

                                select 'a_76488b_c2780' from dual union all

                                 

                                select 'a_76488b_c2780' from dual union all

                                 

                                select 'a_31487b_5542' from dual union all

                                 

                                select 'a_76488b_2780' from dual)

                                 

                                select

                                    str,

                                    substr(

                                     translate(str

                                             ,'0'||translate(str, chr(0)||'0123456789', chr(0))

                                             ,'0'

                                             )

                                         ,-4)

                                    reps

                                from testdata      

                                 

                                STR,REPS

                                "a_76488b_2780","2780"

                                "a_76488b_2780c","2780"

                                "a_76488b_c2780","2780"

                                "a_76488b_c2780","2780"

                                "a_31487b_5542","5542"

                                "a_76488b_2780","2780"

                                • 13. Re: How to fetch data using Substring & Instring
                                  chris227

                                  simple regexp_substr

                                   

                                  with testdata as (

                                  select 'a_76488b_2780' str from dual union all

                                   

                                  select 'a_76488b_2780c' from dual union all

                                   

                                  select 'a_76488b_c2780' from dual union all

                                   

                                  select 'a_76488b_c2780' from dual union all

                                   

                                  select 'a_31487b_5542' from dual union all

                                   

                                  select 'a_76488b_2780' from dual)

                                   

                                  select

                                      regexp_substr(str

                                                  , '(\d{4})\D*$'

                                                  , 1

                                                  , 1

                                                  , null

                                                  , 1

                                      ) subs

                                  from testdata       

                                   

                                  SUBS

                                  "2780"

                                  "2780"

                                  "2780"

                                  "2780"

                                  "5542"

                                  "2780"

                                  • 14. Re: How to fetch data using Substring & Instring
                                    chris227

                                    translate is the right idea but there is probably a simpler way. Take a look at my suggestion.

                                    1 2 Previous Next