sqloracle-databasesubstringinstr

Oracle: instr+substr instead of regexp_substr


I got this query from another post I made which uses REGEXP_SUBSTR() to pull out specific information from a string in oracle. It works good but only for small sets of data. When it comes to tables that have 300,000+ records, it is very slow and I was doing some reading that instr + substr might be faster. The example query is:

SELECT REGEXP_SUBSTR(value, '(^|\|)\s*24=\s*(.*?)\s*(\||$)',  1, 1, NULL, 2)  AS "24",
       REGEXP_SUBSTR(value, '(^|\|)\s*35=\s*(.*?)\s*(\||$)',  1, 1, NULL, 2)  AS "35",
       REGEXP_SUBSTR(value, '(^|\|)\s*47A=\s*(.*?)\s*(\||$)', 1, 1, NULL, 2) AS "47A",
       REGEXP_SUBSTR(value, '(^|\|)\s*98A=\s*(.*?)\s*(\||$)', 1, 1, NULL, 2) AS "98A"
FROM   table_name

Table example:

CREATE TABLE table_name (value ) AS
SELECT '35= 88234.00 | 47A= Shawn | 98A= This is a comment |' FROM DUAL UNION ALL
SELECT '24= 123.00 | 98A= This is a comment | 47A= Derick |' FROM DUAL

Output of query would be:

24 35 47A 98A
88234.00 Shawn This is a comment
123.00 Derick This is a comment

Can someone give me an example of how this same query would look if I was doing instr+substr instead?

Thank you.


Solution

  • SELECT CASE 
           WHEN start_24 > 0
           THEN TRIM(
                  SUBSTR(
                    value,
                    start_24 + 5,
                    INSTR(value, '|', start_24 + 5) - (start_24+5)
                  )
               )
           END AS "24",
           CASE 
           WHEN start_35 > 0
           THEN TRIM(
                  SUBSTR(
                    value,
                    start_35 + 5,
                    INSTR(value, '|', start_35 + 5) - (start_35+5)
                  )
               )
           END AS "35",
           CASE 
           WHEN start_47a > 0
           THEN TRIM(
                  SUBSTR(
                    value,
                    start_47a + 6,
                    INSTR(value, '|', start_47a + 6) - (start_47a+6)
                  )
               )
           END AS "47A",
           CASE 
           WHEN start_98a > 0
           THEN TRIM(
                  SUBSTR(
                    value,
                    start_98a + 6,
                    INSTR(value, '|', start_98a + 6) - (start_98a+6)
                  )
               )
           END AS "98A"
    FROM   (
      SELECT value,
             INSTR(value, '| 24=') AS start_24,
             INSTR(value, '| 35=') AS start_35,
             INSTR(value, '| 47A=') AS start_47a,
             INSTR(value, '| 98A=') AS start_98a
      FROM   (
        SELECT '| ' || value AS value FROM table_name
      )
    );
    

    Which, for your sample data, outputs:

    24 35 47A 98A
    88234.00 Shawn This is a comment
    123.00 Derick This is a comment

    db<>fiddle here