How to split column value in Oracle sql

Question

How to split a column value which is not having space or any other delimiter. I searched the forums but I couldn't able to find the solution for my scenario.

Here is my scenario

ID   Column_Value
011  abcdefgh

The result should be

Column_Value1    Column_Value2
abcd             efgh

Answer 1

If you want to split the string based on the length and number of characters, then use SUBSTR as follows:

SQL> with data(str) as(
  2  select 'abcdefgh' from dual
  3  )
  4  select substr(str, 1, 4) col1,
  5         substr(str, length(substr(str, 1, 4)) +1) col2
  6  from data;

COL1 COL2
---- ----
abcd efgh

SQL>

Above, you could change the value of 4 to your desired value. Remember, both are not mutually exclusive.

Answer 2

Assuming you just want to split a column down the middle, you can achieve this with a combination of substr and length:

SELECT SUBSTR(column_value, 1, LENGTH(column_value) / 2),
       SUBSTR(column_value, LENGTH(column_value) / 2 + 1)
FROM   mytable

SQLFiddle