OK, I'm assuming that both the input and the output are strings. So you just need to nest a couple of functions - one to take the input into a date, and one to take it back to a string

SQL> create table t ( x varchar2(10));

Table created.

SQL>
SQL> insert into t values ('20180327');

1 row created.

SQL>
SQL> select
  2    to_char(
  3       to_date(x,'yyyymmdd'),'dd/mm/yyyy') from t;

TO_CHAR(TO
----------
27/03/2018

By the way, check out Chris's video why you don't want to store dates as strings...its bad




So ideally, you just want to store your dates and dates - and only format them a strings when you need to present them to the user (on screen or in reports etc)