if first name contains a space, hyphen, or apostrophe (" ","-","'") then next letter should capital

[Pandit]

We have a situation in Preprocessor where the queries for my preprocessor is not working as expected.
We have the requirement that if first name contains  a space, hyphen, or apostrophe (" ","-","'") then the first letter should be capital and next letter after  space, hyphen, or apostrophe should be capital, rest all letter should be small.
E.g: if first name = "a ds-av" then display name should be  "A Ds-Av" . I tried with below and other possibilities , but no luck

UPDATE NEWUSERDATA 
SET DISPLAYNAME = CONCAT(
    UPPER(SUBSTRING(firstname, 1, 1)), -- Capitalize the first letter of the firstname
    CASE 
        WHEN firstname REGEXP ' |-|\\'' THEN -- Check if firstname contains space, dash, or apostrophe
            CONCAT(
                UPPER(SUBSTRING(firstname, LOCATE(' ', firstname) + 1, 1)), -- Capitalize the next letter after space, dash, or apostrophe
                LOWER(SUBSTRING(firstname, LOCATE(' ', firstname) + 2)) -- Convert the rest to lowercase
            )
        ELSE LOWER(SUBSTRING(firstname, 2)) -- If no space, dash, or apostrophe, convert the rest to lowercase
    END
) 
WHERE (
    employeetype='Permanent Employee' -- Filter by employeetype
);

Please Advise

[adarshk]

Hi @Pandit 

What is the outcome of the above preprocessor? 

You can narrow down the troubleshooting by implementing each case independently and the merge the conditions using CASE. 

[rushikeshvartak]

UPDATE NEWUSERDATA 
SET DISPLAYNAME = 
CONCAT(
UPPER(SUBSTRING_INDEX(firstname, ' ', 1)),
' ',
UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(firstname, ' ', -1), '-', 1)),
'-',
UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(firstname, '-', -1), '''', 1)),
UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(firstname, '''', -1), ' ', -1))
) WHERE (
    employeetype='Permanent Employee' 
);

[Raghu]

@Pandit  try below

UPDATE NEWUSERDATA
SET DISPLAYNAME = TRIM(
REPLACE(
REPLACE(
REPLACE(
CONCAT(
UPPER(SUBSTRING(DISPLAYNAME, 1, 1)),
LOWER(SUBSTRING(DISPLAYNAME, 2))
),
' ', CONCAT(' ', UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(DISPLAYNAME, ' ', -1), '', 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(DISPLAYNAME, ' ', -1), 2)))
),
'-', CONCAT('-', UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(DISPLAYNAME, '-', -1), '', 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(DISPLAYNAME, '-', -1), 2)))
),
'''', CONCAT('''', UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(DISPLAYNAME, '''', -1), '', 1)), LOWER(SUBSTRING(SUBSTRING_INDEX(DISPLAYNAME, '''', -1), 2)))
)
)
WHERE employeetype = 'Permanent Employee';