2 Replies Latest reply on Sep 11, 2015 12:26 AM by Gaz in Oz

    Extract String after the hyphen symbol

    1533830

      Hello Guru's

       

      I am trying to display the string after the second hyphen position with REGEXP_INSTR, but could not get it, Can any one help here please ...

      No sort operation should be done as it has around 8 lakh records.!

       

      select REGEXP_INSTR(external_device, '[^-]+',1,2) from mydevice;

       

      Data looks like :

      000E72-KB-52111A004361

      000E72-KB-52111A004362

      000E72-KB-52111A004363

      000E72-KB-52111A004365

       

      I need only the last portion of the data after the second hyphen :

      52111A004361

      52111A004362

      52111A004363

      52111A004365

       

      regards

      Rajendra

        • 1. Re: Extract String after the hyphen symbol
          thatJeffSmith-Oracle

          this is a question better for the SQL space

          • 2. Re: Extract String after the hyphen symbol
            Gaz in Oz
            WITH mydevice (external_device) AS
            (SELECT '000E72-KB-52111A004361' FROM dual UNION ALL
             SELECT '000E72-KB-52111A004362' FROM dual UNION ALL
             SELECT '000E72-KB-52111A004363' FROM dual UNION ALL
             SELECT '000E72-KB-52111A004365' FROM dual
            )
            SELECT SUBSTR(external_device, INSTR(external_device, '-', -1) + 1) "SUBSTR(INSTR())",
                   REGEXP_REPLACE(external_device, '^.+-.+-')                   "REGEXP_REPLACE()",
                   REGEXP_REPLACE(external_device, '^(.+)-(.+)-(.+)$', '\3')    "REGEXP_REPLACE(\3)",
                   REGEXP_SUBSTR(external_device, '[0-9A-Z]{12}$')              "REGEXP_SUBSTR()",
                   REGEXP_INSTR(external_device, '[^-]+',1, 2)                  "REGEXP_INSTR()"
            FROM   mydevice;
            

             

            REGEXP_INSTR returns the position of the pattern match, i.e a number, 8 in your case, not the string.

            http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions129.htm

             

            ...and this is nota SQLDeveloper question, it is, as Jeff said, a question for the SQL community. Please bare all that in mind when posting, choose the appropriate community.

             

            Cheers,

             

            Gaz.