Split string using REGEXP_SUBSTR in Oracle SQL gone wrong

Question

Hello i have 3 strings that i want to split and they are all separated with delimiter

string_1 contains names of fields for example: ID, DUE_DATE
string_2 contains values of fields for example: 80781,2026-12-01
string_3 contains types of fields for example:  VARCHAR2,DATE
string_4 contains format of fields for example:  yyyy-mm-dd (for date field)

so i made this query

                with params as (
                  select  'ID|DUE_DATE'         as v_names
                         ,'80781|2026-12-01'    as v_values
                         ,'VARCHAR2|DATE'       as v_types
                         ,'|yyyy-mm-dd'         as v_types_format
                         ,'|' as v_delimiter
                  from dual
                )
                SELECT
                   REGEXP_SUBSTR(v_names, '[^ '||v_delimiter||']+', 1, level) AS v_name
                  ,REGEXP_SUBSTR(v_values, '[^ '||v_delimiter||']+', 1, level) AS v_value
                  ,REGEXP_SUBSTR(v_types, '[^ '||v_delimiter||']+', 1, level) AS v_type
                  ,REGEXP_SUBSTR(v_types_format, '[^ '||v_delimiter||']+', 1, level) as v_format
                FROM params
                CONNECT BY REGEXP_SUBSTR(v_names, '[^ '||v_delimiter||']+', 1, level) IS NOT NULL

I want to get this result ( v_types_format string should bring me null for ID and yyyy-mm-dd for DUE_DATE)

But i get this result

MT0 · Accepted Answer · 2024-04-17 11:24:48Z

You are not matching zero-width terms as [^|]+ matches one-or-more non-pipe characters and in |yyyy-mm-dd the first match is after the pipe and not before.

If you want to match zero-width terms then match each delimiter and the extract the preceding characters since the last match using (.*?)[|] and to match the last match (which won't have a pipe suffix but will have the end of the string) you can use (.*?)$ which you can combine to (.*?)([|]|$).

Like this:

with params as (
  select  'ID|DUE_DATE'         as v_names
         ,'80781|2026-12-01'    as v_values
         ,'VARCHAR2|DATE'       as v_types
         ,'|yyyy-mm-dd'         as v_types_format
         ,'|' as v_delimiter
  from dual
)
SELECT REGEXP_SUBSTR(v_names, '(.*?)(['||v_delimiter||']|$)', 1, level, NULL, 1)
         AS v_name
      ,REGEXP_SUBSTR(v_values, '(.*?)(['||v_delimiter||']|$)', 1, level, NULL, 1)
         AS v_value
      ,REGEXP_SUBSTR(v_types, '(.*?)(['||v_delimiter||']|$)', 1, level, NULL, 1)
         AS v_type
      ,REGEXP_SUBSTR(v_types_format, '(.*?)(['||v_delimiter||']|$)', 1, level, NULL, 1)
         as v_format
FROM   params
CONNECT BY LEVEL < REGEXP_COUNT(v_names, '(.*?)(['||v_delimiter||']|$)')

Which outputs:

V_NAME	V_VALUE	V_TYPE	V_FORMAT
ID	80781	VARCHAR2	null
DUE_DATE	2026-12-01	DATE	yyyy-mm-dd

fiddle

d r · Accepted Answer · 2024-04-17 17:25:25Z

Just as another option - it could be done in an oldfashioned way, using string functions. With large datasets it is quite faster than regexp.

WITH     --  S a m p l e    D a t a :
    definitions (LABELS, VALS, TPS, FORMATS) AS 
        ( Select  'ID, DUE_DATE', '80781,2026-12-01', 'VARCHAR2,DATE', 'yyyy-mm-dd' 
          From Dual
        )

--    S Q L :
SELECT  SubStr( V_NAME,
                InStr(V_NAME, ',', 1, LEVEL + 0) + 1,
                InStr(V_NAME, ',', 1, LEVEL + 1) - InStr(V_NAME, ',', 1, LEVEL + 0) - 1
              ) "V_NAME",
        SubStr( V_VALUE,
                InStr(V_VALUE, ',', 1, LEVEL + 0) + 1,
                InStr(V_VALUE, ',', 1, LEVEL + 1) - InStr(V_VALUE, ',', 1, LEVEL + 0) - 1
              ) "V_VALUE",
        SubStr( V_TYPE,
                InStr(V_TYPE, ',', 1, LEVEL + 0) + 1,
                InStr(V_TYPE, ',', 1, LEVEL + 1) - InStr(V_TYPE, ',', 1, LEVEL + 0) - 1
              ) "V_TYPE", 
        SubStr( V_FORMAT,
                InStr(V_FORMAT, ',', 1, LEVEL + 0) + 1,
                InStr(V_FORMAT, ',', 1, LEVEL + 1) - InStr(V_FORMAT, ',', 1, LEVEL + 0) - 1
              ) "V_FORMAT"

FROM ( Select   ',' || Replace(d.LABELS, ', ', ',') || ',' "V_NAME",      -- adding delimiter before and after 
                ',' || Replace(d.VALS, ', ', ',') || ',' "V_VALUE",       -- and removing possible space after delimiter
                ',' || Replace(d.TPS, ', ', ',') || ',' "V_TYPE",
                Case  When SubStr(d.TPS, 1, 3) In('DAT', 'TIM', 'NUM')    -- for date, timestamp, number types as first nothing 
                      Then '' Else ',,'                                   -- otherwise additional delimiter before
                End || Replace(d.FORMATS, ', ', ',') || ',' "V_FORMAT"
        From    definitions d
    )
 Connect By LEVEL <= GREATEST(  Length(V_NAME) - Length(Replace(V_NAME, ',', '')) - 1,
                                Length(V_VALUE) - Length(Replace(V_VALUE, ',', '')) - 1,
                                Length(V_TYPE) - Length(Replace(V_TYPE, ',', '')) - 1,
                                Length(V_FORMAT) - Length(Replace(V_FORMAT, ',', '')) - 1 
                             )  -- number of elements

/*    R e s u l t :
V_NAME        V_VALUE            V_TYPE          V_FORMAT     
------------- ------------------ --------------- -------------
ID            80781              VARCHAR2                     
DUE_DATE      2026-12-01         DATE            yyyy-mm-dd

psaraj12 · Accepted Answer · 2024-04-19 10:42:34Z

It is always better to explicitly mention the format of the fields

with params as
     (select 'ID|DUE_DATE' as v_names,
             '80781|2026-12-01' as v_values,
             'VARCHAR2|DATE' as v_types,
             'NULL|yyyy-mm-dd' as v_types_format,
             '|' as v_delimiter
        from dual)
    SELECT REGEXP_SUBSTR(v_names, '[^ ' || v_delimiter || ']+', 1, level) AS v_name,
           REGEXP_SUBSTR(v_values, '[^ ' || v_delimiter || ']+', 1, level) AS v_value,
           REGEXP_SUBSTR(v_types, '[^ ' || v_delimiter || ']+', 1, level) AS v_type,
           REGEXP_SUBSTR(v_types_format, '[^ ' || v_delimiter || ']+', 1, level) as v_format
      FROM params
    CONNECT BY REGEXP_SUBSTR(v_names, '[^ ' || v_delimiter || ']+', 1, level) IS NOT NULL

Collectives™ on Stack Overflow

Split string using REGEXP_SUBSTR in Oracle SQL gone wrong

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related