I've recently started my SQL adventure. I was asked to write query which will put the title after the name of person. Title is part of name record. So if somebody surname is 'De Johnson' i need to make 'Johnson (de)' out of it. I need to select only names which contain two words (titled). I have found correct solution to this problem, here is the query:
SELECT SUBSTR(naam, INSTR(naam,' ')+1) || ' (' || LOWER(SUBSTR(naam, 1, INSTR(naam, ' ') -1)) || ')'
WHERE naam like '% %';
Instead of turning this into an equivalent
CONCAT expression, I would suggest a solution that would also:
whereclause and have all the names in the result set.
For this task, regular expressions come in handy:
select regexp_replace(trim(naam), '^(\S+(\s+[^A-Z]\S*)*)\s+(\S.*)$', '\3 (\1)') from medewerkers
The last argument of the function is quite readable in showing the format that is being produced when there is a match.
Here are some test cases:
select id, naam, regexp_replace(trim(naam), '^(\S+(\s+[^A-Z]\S*)*)\s+(\S.*)$', '\3 (\1)') as corrected from (select 1 id, 'De Ridder' as naam from dual union select 2, ' de Meester' from dual union select 3, 'Smits' from dual union select 4, 'Vandenborre ' from dual union select 5, 'Van den Borre' from dual union select 6, ' van der Meulen' from dual union select 7, 'van ''t Oosten' from dual union select 8, 'Van de Walle-Van der Meulen' from dual) order by id;
id | naam | corrected ---+-----------------------------+------------------ 1 | De Ridder | Ridder (De) 2 | de Meester | Meester (de) 3 | Smits | Smits 4 | Vandenborre | Vandenborre 5 | Van den Borre | Borre (Van den) 6 | van der Meulen | Meulen (van der) 7 | van 't Oosten | Oosten (van 't) 8 | Van de Walle-Van der Meulen | Walle-Van der Meulen (Van de)