2 Replies Latest reply on Sep 16, 2019 9:18 AM by Bommi

    How to fetch the records based on particular format

    Bommi

      Hi Experts,

       

      We need to fetch data from a custom table ABC based on a particular format that is available in field

       

      Example, we have a filed Description in ABC. We need to fetch records only when Description field contains data in format p.nnnnn/nnn-nnn/nnn. Example the 'n' can be any numbers. Like: p.11111/111-111/111 or p.22222/222-222/222.

       

      The total numbers after p. is 5 and after that / and then 3 numbers and then - and then again 3 number and then / and then 3 numbers.

      The format is fixed.

       

      Is there any way to fetch records based on this.

       

       

      Thanks in Advance,

      Bommi

        • 1. Re: How to fetch the records based on particular format
          cormaco

          You can do this using regular expressions:

          https://www.regular-expressions.info/

           

          with abc(id,description) as (
              select 1,'p.11111/111-111/111' from dual union all
              select 2,'p.22222/222-222/222' from dual
          )
          select * from abc where regexp_like(description,'p\.\d{5}/\d{3}-\d{3}/\d{3}')
          
          
                  ID DESCRIPTION        
          ---------- -------------------
                   1 p.11111/111-111/111
                   2 p.22222/222-222/222
          
          
          
          1 person found this helpful
          • 2. Re: How to fetch the records based on particular format
            Bommi

            Hi Cormaco,

             

            As you suggested, I used below 2 SELECT statements (a minor change with your SELECT) to fetch the records which starts with that format and which are not with that format.

            It worked perfectly

             

            select * from abc where regexp_like(description,'^p\.\d{5}/\d{3}-\d{3}/\d{3}','i');

             

            select * from abc where NOT regexp_like(description,'^p\.\d{5}/\d{3}-\d{3}/\d{3}','i');

             

            Very thanks for your immediate help

             

            Regards,

            Bommi

            1 person found this helpful