Captalizing Each Word of an existing table

Question

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:

And why did that query not work for you? It does exactly what you want. — Nick, Oct 21 '19 at 03:00
@Nick Some of the Artists don't have last names and its doing the following:[IMG](https://i.imgur.com/EhvNFmF.png) The query is putting a extra letter in the first name — J S, Oct 21 '19 at 03:10
OK. Please [edit] your question with the query you are using, and *all* the cases that don't work as you expect, and I will reopen it. — Nick, Oct 21 '19 at 03:11
@Nick I added only two that came to my mind but there are way more Artists that only have first name and the query is putting an extra character in the front. — J S, Oct 21 '19 at 03:15
@Nick I already edited. Read the very last line of my original post. — J S, Oct 21 '19 at 03:36

score 1 · Accepted Answer · answered Oct 21 '19 at 03:56

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

Captalizing Each Word of an existing table

1 Answers1