How do I split column into 2 based on certain conditions in Oracle SQL?

I have a table of 250k worth of data and out of this data I have 1000 rows that have the same data in every column bar one reference column which is different.

What I would like to do is split the reference column if certain conditions are met, and if these conditions are not met, the reference value can stay as it is.

The following is my create table and insert statements:

CREATE TABLE "BU_TABLE" 
   (   
    "NAME" VARCHAR2(255 BYTE), 
    "TEL_NO" VARCHAR2(255 BYTE), 
    "POST_CODE" VARCHAR2(8 BYTE), 
    "REF_NO" VARCHAR2(255 BYTE)
;

The reason I have set the Tel_No and the Ref_No column as Varchar2 is because of the spaces as when numbers have spaces then a) it will prompt an Invalid Number error when inserting b) Tel_No at the moment with the example data doesn’t have this issue but as more data is added I might have this issue going forward.

Insert statements:

Insert into BU_TABLE (NAME,TEL_NO,POST_CODE,REF_NO) values ('Damian','7900123456','ME1 2BC','12345678 1234567891234');
Insert into BU_TABLE (NAME,TEL_NO,POST_CODE,REF_NO) values ('Graeme','7900789012','ME1 2DE','12 345 5678901234567');
Insert into BU_TABLE (NAME,TEL_NO,POST_CODE,REF_NO) values ('Sarah','7900456789','ME1 2FG','90123456 890123456789');

Now what I would like for the BU_TABLE to stay as it is, with the raw data. I would like to create another table of the back of this table to split out the Ref_No, so where I have a sequence of 8 numbers, a space and then 13 numbers. Wherever this pattern occurs I would like to duplicate the row and give me the following:

Name   | Tel_No     | Post_Code | Ref_No
Damian | 7900123456 | ME1 2BC   | 12345678
Damian | 7900123456 | ME1 2BC   | 1234567891234

The rows where the required code did not find the sequence, the rows of data will stay the same in the table so my final table will look like this. This shows the new data split where Damian’s 2 reference numbers have been split and now show up as 2 rows and Graeme and Sarah have their original reference numbers as their sequences did not meet the criteria.

Final table:

Name   | Tel_No     | Post_Code | Ref_No
Graeme | 7900789012 | ME1 2DE   | 12 345 5678901234567
Sarah  | 7900456789 | ME1 2FG   | 90123456 890123456789
Damian | 7900123456 | ME1 2BC   | 12345678
Damian | 7900123456 | ME1 2BC   | 1234567891234

Hopefully my requirements are clear in terms of what I am looking for.

Apologies as there is another post with the same question: How do I copy a row and split one of the columns based on certain criteria in Oracle SQL?

but the question got closed as I was asked to add my create and insert statements to make my requirements clearer, hence I created a new question.

Answer

You could do this with a union all. I show a different approach below – using the common unpivoting technique from the old days (before Oracle introduced the unpivot operator). This will be more efficient than solutions using union all, as it requires only one full table scan.

I don’t show the creation of a table; I show a select statement that you can use to populate your new table.

select bu.name, bu.tel_no, bu.post_code,
       case when regexp_like(bu.ref_no, '^d{8} d{13}$') 
            then
                 case t.c when 1 then substr(bu.ref_no, 1, 8) 
                                 else substr(bu.ref_no, 10)   end
            else bu.ref_no
       end as ref_no
from   bu_table bu join (select 1 as c from dual union all select 2 from dual) t
                   on t.c = 1 or regexp_like(bu.ref_no, '^d{8} d{13}$')
;

NAME    TEL_NO      POST_CODE    REF_NO
------  ----------  ---------  -----------------------
Damian  7900123456  ME1 2BC    12345678
Graeme  7900789012  ME1 2DE    12 345 5678901234567
Sarah   7900456789  ME1 2FG    90123456 890123456789
Damian  7900123456  ME1 2BC    1234567891234