Formatting names in a single column in sql oracle

I need to help. I am new in tha oracle. I have a simple problem.

My table:

create table employee (name varchar2(30));
insert into employee values ('kevin durant');
insert into employee values ('michael JoRdaN');
insert into employee values (' dWyaNe WAdE');
insert into employee values ('james Harden');
insert into employee values ('pAuL ThomaS AnDersoN');

The format I want is as follows:

Durant K.
Jordan M.
Wade D.
Harden J.
Anderson P.T.

I tried many methods but I could not reach the result. How can I get an output as I want?

Answer

TRIM will remove leading and trailing spaces. Then INITCAP will capitalise the first letter of each word and put the rest into lower case. Then you can use regular expressions to replace each forename with its abbreviation and to swap the forename and surname:

SELECT REGEXP_REPLACE(
         REGEXP_REPLACE(
           INITCAP(TRIM(name)),
           '(S)S*s+',         -- Match a forename with a trailing space
           '1.'                 -- And replace it with the abbreviation
         ),
         '(.*.)(.+)',           -- Match the substrings before and after the last
                                 -- full stop
         '2 1'                 -- And swap them.
       ) AS Name
FROM   employee

Which, for your test data, outputs:

| NAME          |
| :------------ |
| Durant K.     |
| Jordan M.     |
| Wade D.       |
| Harden J.     |
| Anderson P.T. |

db<>fiddle here