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

As the question,

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

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

CodePudding user response：

Assuming the following table:

create table my_table (my_column varchar2(30));
insert into my_table values ('(12345)some_string');
commit;

1) Add a new column to the table

alter table my_table add new_column number;`

2) Fill the new column

update my_table set new_column = regexp_substr(my_column, '^\(([1-9] )\)', 1, 1, NULL, 1);

3) Update the original column

update my_table set my_column = regexp_replace(my_column, '^\([1-9] \)', '');

CodePudding user response：

With sample data you posted, this could be one option (line #5 onward):

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

SQL>