How to split columns into two in oracle sql based in character?

You already get with your Query everything before '@'. → You just need everything after '@'.

This query provides exactly the desired output for the sample data provided:

SELECT 
  replace(REGEXP_SUBSTR(Customer_ID,'[^|@]+',1,1),'$','')  as Customer_ID_1 , -- everything before '@' and remove '$'
  regexp_replace(Customer_ID,'^([0-9]+[$@]*)','')  as Customer_ID_2 , -- everything after '$@' or '@$' and remove this match
  replace(REGEXP_SUBSTR(Customer_post,'[^|@]+',1,1),'$','')  as  Customer_post_1, -- everything after '$@' or '@$' and remove this match
  regexp_replace(Customer_post,'^([0-9]+[$@]*)','')  as  Customer_post_2, -- everything after '@'
FROM Customer_tbl
;

How it works :

• ^([0-9]+[$@]*) → matches a string that begins like "12985", "548357$@" or "1@$"
• At least one number must be followed by "$@" or "@$"
• The "regexp_replace" can replace this match with something. In this case I replaced the match with an empty string ''. So that you get only the last part.
• When "regexp_replace" does not find a match for the regular expression, NULL will return.

If you want to test such queries yourself (e.g if you have problems with a special string), you can do it as follows:

select regexp_replace('201$@29$@29$@29','^([0-9]+[$@]*)','') from dual;

Just put the problematic string in

select regexp_replace('Put_your_String_here' ....

Here can you find some useful references for oracle regexp-functions:

• regexp_substr https://www.techonthenet.com/oracle/functions/regexp_substr.php
• regexp_replace https://www.techonthenet.com/oracle/functions/regexp_replace.php
• regexp_instr https://www.techonthenet.com/oracle/functions/regexp_instr.php

... and of course the official oracle reference:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/REGEXP_REPLACE.html#GUID-EA80A33C-441A-4692-A959-273B5A224490