CODEWITHSUNDEEP
sqloracle-database
nethero
nethero

Reputation: 756

SQL reordered words

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)) || ')' 
FROM medewerkers 
WHERE naam like '% %';

Now is my question, is it possible to make it more verbose? I'm really more into using CONCAT in place of || etc. I don't understand concept of mixing verbosity of SQL with sings. I know that this query doesn't have any sense since SQL is not used to format the output, but I'm doing it for school (they can't teach even things as easy as SQL).

Upvotes: 1

Views: 83

Answers (1)

trincot
trincot

Reputation: 350310

Instead of turning this into an equivalent CONCAT expression, I would suggest a solution that would also:

  • Move more little words to the end on the condition that they do not start with a capital, except for the first one. For example: "Van den Heuvel" would become "Heuvel (Van den)";
  • Trim spaces from the beginning and the end: this will avoid strange effects on names that were stored with spaces at the start or the end.
  • Return a name unchanged if it has no words to move into brackets. This is useful, as you can then exclude the where clause 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.

NB: Depending on language settings (namely NLS_SORT, with case-insensitive sort), it might be necessary to add more arguments to preg_replace to enforce case-sensitivity:

select regexp_replace(trim(naam), '^(\S+(\s+[^A-Z]\S*)*)\s+(\S.*)$', '\3 (\1)', 1, 0, 'c')
from   medewerkers

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;

Outputs:

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)

Upvotes: 2

Related Questions