[Solved] How to do the c# string.split() in oracle

by


Replace the prefixes you want to remove, then find the index of the first and second underscores and then find the substring between those two separators:

Oracle Setup:

CREATE TABLE your_table ( value ) AS
SELECT 'DUM_EI_AO_L_5864_Al Meena Tower' FROM DUAL UNION ALL
SELECT 'EI_AE_L_5864_Al radha Tower' FROM DUAL

Query:

SELECT value,
       SUBSTR( replaced_value, first_separator + 1, second_separator - first_separator - 1 )
         AS second_term
FROM   (
  SELECT value,
         replaced_value,
         INSTR( replaced_value, '_', 1, 1 ) AS first_separator,
         INSTR( replaced_value, '_', 1, 2 ) AS second_separator
  FROM   (
    SELECT value,
           REPLACE(
             REPLACE(
               value,
               'PRJ_'
             ),
             'DUM_'
           ) AS replaced_value
    FROM   your_table
  )
)

Output:

VALUE                           | SECOND_TERM
:------------------------------ | :----------
DUM_EI_AO_L_5864_Al Meena Tower | AO         
EI_AE_L_5864_Al radha Tower     | AE

Query 2:

You can also use a regular expression:

SELECT value,
       REGEXP_SUBSTR( value, '(DUM_|PRJ_)?.*?_(.*?)_', 1, 1, NULL, 2 ) AS second_term
FROM   your_table

Output:

VALUE                           | SECOND_TERM
:------------------------------ | :----------
DUM_EI_AO_L_5864_Al Meena Tower | AO         
EI_AE_L_5864_Al radha Tower     | AE

db<>fiddle here

0

solved How to do the c# string.split() in oracle