What is wrong with my Select so that my case works again

Question

Hi my select here is not working. Can someone tell me what is wrong with my syntax? I would like to get 3 columns with different results from the name, upper,lower and initcap but when the name dr. has then only the next 3 letters are given as upper.

select mitarbeitername,
    case 
    when regexp_like(mitarbeitername, '^Dr.*')  then  upper(substr(mitarbeitername,4))
    else upper(substr(mitarbeitername, 1,3))
     , lower(substr(mitarbeitername, 1,3) )
     , initcap(substr(mitarbeitername, 1,3)) 
    end Name   
from mitarbeiter;

Answer 1

I would like to get 3 columns with different results from the name, upper, lower and initcap but when the name dr. has then only the next 3 letters are given as upper.

A CASE expression outputs a single scalar value; it cannot output three values. If want three values then use three CASE expressions:

SELECT mitarbeitername,
       CASE
       WHEN mitarbeitername LIKE 'Dr.%')
       THEN UPPER(SUBSTR(mitarbeitername,4))
       ELSE UPPER(SUBSTR(mitarbeitername, 1,3))
       END AS upper_name,
       CASE
       WHEN mitarbeitername LIKE 'Dr.%')
       THEN NULL
       ELSE LOWER(SUBSTR(mitarbeitername, 1,3))
       END AS lower_name,
       CASE
       WHEN mitarbeitername LIKE 'Dr.%')
       THEN NULL
       ELSE INITCAP(SUBSTR(mitarbeitername, 1,3))
       END AS initcap_name
FROM   mitarbeiter;

Note: You do not need to use regular expressions to match string starting with Dr; you can perform a simple string comparison using LIKE, which will be much faster. If you did want to use regular expressions then you want the pattern ^Dr\. or ^Dr\..* as an unescaped . will match any character.