how to split one string column of `(Ab56.12345)some_string` to two column `Ab.12345`, `some

As the question,

how to split one string column of (Ab56.12345)some_string to two-column Ab56.12345and some_string in Oracle?

Notice: Not all the columns are (Ab56.12345)some_string, part of columns are only some_string without (Ab56.12345), the two columns are null and some string

This answer from @Littlefoot can't solve my question now

SQL> with test (col) as
  2    (select '(12345)some_string' from dual union all
  3     select 'another_string'     from dual
  4    )
  5  select regexp_substr(col, '\d ') col1,
  6         substr(col, instr(col, ')')   1) col2
  7  from test;
```

COL1               COL2
------------------ ------------------
12345              some_string
                   another_string

CodePudding user response：

We can use REGEXP_SUBSTR with a capture group here:

SELECT
    REGEXP_SUBSTR(col, '\((. ?)\)', 1, 1, NULL, 1) AS COL1,
    REGEXP_REPLACE(col, '\(. ?\)', '') AS COL2
FROM test;

Demo

CodePudding user response：

Or, without regular expressions, using substr instr combination:

SQL> WITH test(col) AS (
  2      SELECT '(12345)some_string' FROM dual UNION ALL
  3      SELECT 'another_string'     FROM dual UNION ALL
  4      SELECT '(Ab56.12345)string' FROM dual
  5  )
  6  SELECT
  7    replace(replace(substr(col, 1, instr(col, ')')), '(', ''), ')', '') col1,
  8    substr(col, instr(col, ')')   1) col2
  9  FROM test;

COL1               COL2
------------------ ------------------
12345              some_string
                   another_string
Ab56.12345         string

SQL>