using Oracle SQL - regexp_substr to split a record

Question

I need to split the record for column CMD.NUM_MAI which may contain ',' or ';'. I did this but it gave me an error:

  SELECT REGEXP_SUBSTR (expression.num_mai,
                      '[^;|,]+',
                      1,
                      LEVEL)
  FROM (SELECT CMD.num_cmd,
               (SELECT COMM.com
                  FROM COMM
                 WHERE COMM.cod_soc = CMD.cod_soc AND COMM.cod_com = 'URL_DSD')
                  AS cod_url,
               NVL (CONTACT.nom_cta, TIERS.nom_ct1) AS nom_cta,
               NVL (CONTACT.num_mai, TIERS.num_mai) AS num_mai,
               NVL (CONTACT.num_tel, TIERS.num_tel) AS num_tel,
               TO_CHAR (SYSDATE, 'hh24:MI') AS heur_today
          FROM CMD, TIERS, CONTACT
         WHERE     (    (CMD.cod_soc = :CMD_cod_soc)
                    AND (CMD.cod_eta = :CMD.cod_eta)
                    AND (CMD.typ_cmd = :CMD.typ_cmd)
                    AND (CMD.num_cmd = :CMD.num_cmd))
               AND (TIERS.cod_soc(+) = CMD.cod_soc)
               AND (TIERS.cod_trs(+) = CMD.cod_trs_tra)
               AND (TIERS.cod_soc = CONTACT.cod_soc(+))
               AND (TIERS.cod_trs = CONTACT.cod_trs(+))
               AND (CONTACT.lib_cta(+) = 'EDITION')) experssion
CONNECT BY REGEXP_SUBSTR (expression.num_mai,'[^;|,]+',1,LEVEL)

The regex of the form '[^,|;]+' will return the wrong element if the element you want is after a NULL element. See this other post for more info: stackoverflow.com/questions/30192688/… — Gary_W
– Gary_W, Commented May 26, 2015 at 18:36

Maheswaran Ravisankar · Accepted Answer · 2015-04-29 16:27:26Z

2

Error 1:

The expression in CONNECT BY clause is unary. You have to specify both left and right hand side operands.

Try something like,

CONNECT BY REGEXP_SUBSTR (expression.num_mai,'[^;|,]+',1,LEVEL) IS NOT NULL

Error 2:

Your bind variable name is wrong. Ex: :CMD_cod_eta Perhaps you wanted this way!

(    (CMD.cod_soc = :CMD_cod_soc)
                    AND (CMD.cod_eta = :CMD_cod_eta)
                    AND (CMD.typ_cmd = :CMD_typ_cmd)
                    AND (CMD.num_cmd = :CMD_num_cmd))

answered Apr 29, 2015 at 16:27

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ITE Over a year ago

It worked thanks, the problem was the missing 'IS NOT NULL' .

tbone · Accepted Answer · 2015-04-29 19:42:38Z

This is a common question, I'd put into a function, then call it as needed:

CREATE OR REPLACE function fn_split(i_string in varchar2, i_delimiter in varchar2 default ',', b_dedup_tokens in number default 0)
return sys.dbms_debug_vc2coll
as
  l_tab sys.dbms_debug_vc2coll;
begin
  select regexp_substr(i_string,'[^' || i_delimiter || ']+', 1, level)
  bulk collect into l_tab
  from dual
  connect by regexp_substr(i_string, '[^' || i_delimiter || ']+', 1, level) is not null
  order by level;

  if (b_dedup_tokens > 0) then
    return l_tab multiset union distinct l_tab;
  end if;
  return l_tab;
end;
/

This will return a table of varchar2(1000), dbms_debug_vc2coll, which is a preloaded type owned by SYS (or you could create your own type using 4000 perhaps). Anyway, an example using it (with space, comma, or semi-colon used as delimiters):

with test_data as (
  select 1 as id, 'A;test;test;string' as test_string from dual
  union
  select 2 as id, 'Another string' as test_string from dual
  union
  select 3 as id,'A,CSV,string' as test_string from dual
)
select d.*, column_value as token
from test_data d, table(fn_split(test_string, ' ,;', 0));

Output:

ID  TEST_STRING TOKEN
1   A;test;test;string  A
1   A;test;test;string  test
1   A;test;test;string  test
1   A;test;test;string  string
2   Another string  Another
2   Another string  string
3   A,CSV,string    A
3   A,CSV,string    CSV
3   A,CSV,string    string

You can pass 1 instead of 0 to fn_split to dedup the tokens (like the repeated "test" token above)

Collectives™ on Stack Overflow

using Oracle SQL - regexp_substr to split a record

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related