10 Replies Latest reply: Jan 8, 2013 6:54 PM by 863252 RSS

    How To Extract a Fixed Length Number from a String

    863252
      Hi
      I have a requirement to find all the 9 digit numbers from a string with any delimiter.
      I have tried
      SELECT REGEXP_SUBSTR(REPLACE('12345678978979798ZXC3ASD456FGH8TED63456789123',' '),'[[:digit:]]{9}') FROM dual
      WHERE REGEXP_SUBSTR(REPLACE('12345678978979798ZXC3ASD456FGH8TED63456789123',' '),'[[:digit:]]{9}') IS NOT NULL;

      It Gives me 123456789
      But I want output as

      123456789 634567891
      Please Help.

      Thanks
      G
        • 1. Re: How To Extract a Fixed Length Number from a String
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: How To Extract a Fixed Length Number from a String
            NSK2KSN
            SELECT REGEXP_SUBSTR(REPLACE('12345678978979798ZXC3ASD456FGH8TED63456789123',' '),'[[:digit:]]{9}',1,1)||REGEXP_SUBSTR(REPLACE('12345678978979798ZXC3ASD456FGH8TED63456789123',' '),'[[:digit:]]{9}',1,2) FROM dual
            WHERE REGEXP_SUBSTR(REPLACE('12345678978979798ZXC3ASD456FGH8TED63456789123',' '),'[[:digit:]]{9}') IS NOT NULL;
            • 3. Re: How To Extract a Fixed Length Number from a String
              863252
              Thanks a lot for your response.
              I also need to distinguish between 2 numbers. I have a Huge String and need to extract all numbers with space or comma in between.
              For the above output should be
              123456789{delimiter as space} 634567891 .
              Thanks a lot once again.

              G
              • 4. Re: How To Extract a Fixed Length Number from a String
                ranit B
                Use Back-referencing to extract all sub-strings of a particular pattern :
                Ranit>> with xx as(
                  2       select '12345678978979798ZXC3ASD456FGH8TED63456789123' x from dual
                  3  )
                  4  select
                  5  replace(regexp_replace(x,'([[:digit:]]{9})|.','.\1'),'.')
                  6  from xx;
                
                REPLACE(REGEXP_REP                                                                                                                
                ------------------                                                                                                                
                123456789634567891   
                @NSK - What 'll happen if a 3 ^rd^ sub-string exists? You've hardcoded for 2 SUBSTRs.
                • 5. Re: How To Extract a Fixed Length Number from a String
                  ranit B
                  Hi Garima,

                  Hope this meets your requirement :
                  Ranit>> with xx as(
                    2       select '12345678978979798ZXC3ASD456FGH8TED63456789123' x from dual
                    3  )
                    4  select
                    5  TRIM(regexp_replace(regexp_replace(x,'([[:digit:]]{9})|.','.\1'),'(\.)+',' '))
                    6  from
                    7  xx;
                  
                  TRIM(REGEXP_REPLACE                                                                                                               
                  -------------------                                                                                                               
                  123456789 634567891    
                  Please let me know if you have any further concerns.
                  • 6. Re: How To Extract a Fixed Length Number from a String
                    863252
                    Hi Ranit
                    Yes my string can have multiple sub strings. The query mentioned by you also doesn't distinguish between 2 numbers.
                    I need to Extract all numbers with some delimiter and then process each of these numbers individually.

                    Thanks
                    Garima
                    • 7. Re: How To Extract a Fixed Length Number from a String
                      863252
                      Thanks a lot Ranit.
                      This helps.
                      • 8. Re: How To Extract a Fixed Length Number from a String
                        ranit B
                        Glad that it helped, Garima.

                        Thanks.
                        • 9. Re: How To Extract a Fixed Length Number from a String
                          Solomon Yakobson
                          860249 wrote:
                          and then process each of these numbers individually.
                          Then you need each of them as a separate row:
                          with t as(
                                    select '12345678978979798ZXC3ASD456FGH8TED63456789123' str from dual
                                   )
                          select  str,
                                  regexp_substr(str,'\d{9}',1,column_value) sub_str
                            from  t,
                                  table(
                                        cast(
                                             multiset(
                                                      select  level
                                                        from  dual
                                                        connect by regexp_instr(str,'\d{9}',1,level) > 0
                                                     )
                                             as sys.OdciNumberList
                                            )
                                       )
                          /
                          
                          STR                                           SUB_STR
                          --------------------------------------------- ----------
                          12345678978979798ZXC3ASD456FGH8TED63456789123 123456789
                          12345678978979798ZXC3ASD456FGH8TED63456789123 634567891
                          
                          SQL>
                          SY.
                          • 10. Re: How To Extract a Fixed Length Number from a String
                            863252
                            Thanks a lot Solomon.
                            This really helps..