fix regexp_substr expression-CodePudding

Appreciate help to fix the regexp -

with data as (
  select '1-2' test_col from dual union all
  select '   1   -   2' test_col from dual union all
  select '1 -  ' test_col from dual union all
  select '-2' test_col from dual union all
  select '-' test_col from dual union all
  select '' test_col from dual 
  )select regexp_substr(test_col,'[^- ] ',1) col1,
regexp_substr(test_col,'[^- ] ',2) col2
from data;

Expected output -

COL1	COL2
1	2
1	2
1	null
null	2
null	null
null	null

Fiddle.

CodePudding user response：

You can use:

select regexp_substr(test_col,'^\s*([^-[:space:]]*)\s*-\s*([^-[:space:]]*)\s*$', 1, 1, NULL, 1) col1,
       regexp_substr(test_col,'^\s*([^-[:space:]]*)\s*-\s*([^-[:space:]]*)\s*$', 1, 1, NULL, 2) col2
from   data;

Which, for the sample data:

CREATE TABLE data (test_col) as
  select '1-2'          from dual union all
  select '   1   -   2' from dual union all
  select '1 -  '        from dual union all
  select '-2'           from dual union all
  select '-'            from dual union all
  select ''             from dual;

Outputs:

COL1	COL2
1	2
1	2
1	null
null	2
null	null
null	null

fiddle

CodePudding user response：

This may be of help as well.

with data as 
(
  select '1-2' test_col from dual union all
  select '   1   -   2' test_col from dual union all
  select '1 -  ' test_col from dual union all
  select '-2' test_col from dual union all
  select '-' test_col from dual union all
  select '' test_col from dual 
)
select 
test_col, 
nvl(trim(substr(test_col, 1, regexp_instr(test_col, '( -)( )|(-)( )')-1)), 'null') col1,
nvl(regexp_replace(substr(test_col, regexp_instr(test_col, '( -)( )|(-)( )')   1), '-(*)| (*)', null), 'null') col2
from data;

Thanks ~ Arnab P