CODEWITHSUNDEEP
oracle-databasecalculated-columns
Marco
Marco

Reputation: 97

Oracle: Split data from one column into two columns based on data

I have a strange situation. a) I have to create a table B from Table A plus some custom columns. For example. Table B should have few columns of table A and some additional columns(These are static values like NULL,static string and system timestmaps).

b) One column in Table A needs to be split into Two columns in Table B example: Data in Column X is [A1234, B5678, 0000, 1111] Table B should have two columns AlphaColumn[A1234, B5678], NumberishColumn[0000, 1111] The difference is : First letter of the data can be alphabet. Thats the only distinguishing criteria enter image description here How can I do this in one query?

Upvotes: 0

Views: 322

Answers (1)

MT0
MT0

Reputation: 168681

You can use a CASE expression and simple string functions:

INSERT INTO table_b(firstname, lastname, alphacolumn, numberishcolumn)
SELECT firstname,
       lastname,
       CASE
       WHEN SUBSTR(employeeid, 1, 1) BETWEEN '0' AND '9'
       THEN NULL
       ELSE employeeid
       END,
       CASE
       WHEN SUBSTR(employeeid, 1, 1) BETWEEN '0' AND '9'
       THEN employeeid
       ELSE NULL
       END
FROM   table_a;

Or, you could create table_b as a VIEW instead of another table.

Upvotes: 0

Related Questions