This discussion is archived
10 Replies Latest reply: Jan 8, 2013 4:54 PM by 863252 RSS

How To Extract a Fixed Length Number from a String

863252 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot Ranit.
    This helps.
  • 8. Re: How To Extract a Fixed Length Number from a String
    ranit B Expert
    Currently Being Moderated
    Glad that it helped, Garima.

    Thanks.
  • 9. Re: How To Extract a Fixed Length Number from a String
    Solomon Yakobson Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot Solomon.
    This really helps..

Legend

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