Captalizing Each Word of an existing table

I have a table called ‘Artists’. There is a column called Artist in ‘Artists’.

Artist
firstname lastname
Firstname lastname
Firstname Lastname
firstname

I tried the query on this Post

I want to captalize first letter of each artist’s firstname & lastname in my ‘Artists’ table. Expecting:

Artist
Firstname Lastname
Firstname Lastname
Firstname Lastname
Firstname

Some of my Artists don’t have firstname and the query is putting an extra character in beginning:

Answer

One way to work around this issue is to use a CASE expression, choosing the formula from the other question when Artist has two names, and just capitalising the first letter when there is only one name:

SELECT 
    CASE WHEN Locate(' ', Artist) = 0 THEN 
         -- firstname only
         CONCAT(UPPER(SUBSTRING(Artist, 1, 1)), 
                LOWER(SUBSTRING(Artist, 2)))
    ELSE
         -- firstname and lastname
         CONCAT(UPPER(SUBSTRING(Artist, 1, 1)),
                LOWER(SUBSTRING(Artist, 2, Locate(' ', Artist)-1)),
                UPPER(SUBSTRING(Artist, Locate(' ', Artist)+1,1)),
                LOWER(SUBSTRING(Artist, Locate(' ', Artist)+2)))
    END AS Artist
FROM Artists

Output:

Artist
Firstname Lastname
Firstname Lastname
Firstname Lastname
Firstname Lastname
Firstname
Firstname

Demo on dbfiddle