Title case for field with first and second names 2

[sinbadly]

When I was concat-ing two fields together, fname and sname, the following code gave the title case (First letter of fname and sname capped). But now the csv files I download have a single field wherein fname and sname are together.

How can the code be adapted, please, to give title case for the two names in the field? Sometimes there are 3 names in the field, but I don't suppose that can be catered for, too.(?)

I think this is what I used to do -

CONCAT(UPPER(SUBSTRING(Manager, 1,1)),
LOWER(SUBSTRING(Manager,2)) ' ',
UPPER(SUBSTRING(Manager, 1,1)),
LOWER(SUBSTRING(Manager,2)))

I am trying –

CONCAT(UPPER(SUBSTRING(Manager,1,1)),
LOWER(SUBSTRING(Manager,2)))
AS n,

… but it fails to upper-case the first letter of the second word.

 

[TonyGroves]

I think what you would need to do is to specifically replace " a" with " A" and so on:
[tt]
REPLACE(
...
REPLACE(
REPLACE(
REPLACE(
CONCAT(UPPER(SUBSTRING(Manager,1,1)),LOWER(SUBSTRING(Manager,2))),
" a"," A"),
" b"," B"),
" c"," C"),
...
" z"," Z")
as n
[/tt]
If you want to cater properly for such names as O'Sullivan, McKenna, etc., the replacement list would have to be extended further.

 

[sinbadly]

Thanks, enormously, Tony. In fact, it was from you that I had that very useful earlier code.

Do I code the query exactly like this, or do I write out the alphabet following your style?

By the by, I use it in fantasy soccer league results in a high school website that I run. What they've done this year is to have entries in the name field like 'Ms e gallagher' and 'scott and jay holmes sandom' - though the great majority are just two names.

 

[feherke]

Hi

I would query those data, change the names in a regular expression capable programming/scripting language and update the table. You should do this only once, then will have beautified named all the time. If need to keep the names with the original orthography, then would consider using additional field or another table for the title cased names.

Feherke.

http://rootshell.be/~feherke/

 

[TonyGroves]

You need to write out the whole alphabet (I was too lazy to do it).

As you say, you won't get 100% success, but maybe afterwards you could run a query so that you can visually check all the names with more than two words.