Formatting names in a single column in sql oracle

user_13570710 0
Viewed 38

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?

formatting oracle sql
1 Answers

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

user_15092640
Related