Capitalise Name and Initials in a String

Question

I have a number of records that store names in varying combinations, e.g. -

first_name  | last_name
Joe         | BLOGGS
Jane Louise | SMITH
JB          | Smith
A B         | Jones

I need to displays these names in proper case -

full_name
Joe Bloggs
Jane Louise Smith
JB Smith
AB Jones

I know PHP has various built-in functions for converting string to uppercase, lowercase, first letter capitalised, etc but I can't find anything that will handle initials correctly.

Does anyone have a script that will do this? Alternatively, if it is possible in MySQL then that will be an option.

Have you looked into [ucfirst](http://php.net/manual/en/function.ucfirst.php) and [ucwords](http://php.net/manual/en/function.ucwords.php)? — aynber, Jul 25 '17 at 14:52
`A B | Jones` converted to `AB Jones`? why space between and b is removed? And what you exactly looking for?(input -output relation ?) — Alive to die - Anant, Jul 25 '17 at 14:54
@Alive to Die I think this is exactly what he wants to achieve - if name is only initials then join (remove space) if full name then keep as it is with first letter uppercase. — ArtOsi, Jul 25 '17 at 14:57
Just accept what the user types in; you can't set rules to names, it doesn't work; e.g. John McDonald, Will O'Connor or 周潤發 — CD001, Jul 25 '17 at 14:57
it's irrelevant that if i put my name `Alive To | Die` then it will converted to `AliveTo Die` — Alive to die - Anant, Jul 25 '17 at 14:59
Ideally, it would combine initials but that is not really an issue. All I really want is for names to be capitalised correctly (as per UK formats). So `A B Jones` can remain as `A B Jones`, `Jane Louise SMITH` should be `Jane Louise Smith`, `JB SMITH` should be `JB Smith` — JASSY, Jul 25 '17 at 15:05
That might be a bit difficult. How can you differentiate initials from 2-letter names like Al and Ed? — aynber, Jul 25 '17 at 15:06
@JASSY there is **no** UK format... you can just as easily be *Mr. Barrington-Smythe*, *Jane McDonald*, *Edwin van der Velt* or 周潤發 (in which case the "last" name comes first) and live in the UK. I've posted links to this article several times before: [Falsehoods Programmers Believe About Names](http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/) - in short, you **can't** accurately reformat a name someone has entered, you *will* make assumptions that are wrong. — CD001, Jul 25 '17 at 15:11
@aynber - that's the challenge I'm having. I was hoping someone had come up with a solution but wasn't holding my breath! — JASSY, Jul 25 '17 at 15:13
@CD001 The data I am displaying is sourced from Companies House, rather than being typed in on my application. It appears that they store it in `first_name` (mostly formatted correctly) and `last_name` (all uppercase). Unfortunately, some of the people are actually companies which are being stored as `U S MANAGEMENT INC` or `PRICELESS SECRETARIES LLP`. If I convert the first character of these to uppercase and lowercase the remainder, it will output incorrect formats -`U S Management Inc` or `Priceless Secretaries Llp` — JASSY, Jul 25 '17 at 15:22
You might instead want to see if the [whole word is uppercase](https://stackoverflow.com/questions/4211875/check-if-a-string-is-all-caps-in-php) and longer than a certain length (4? 5?), and then do ucfirst() on it. — aynber, Jul 25 '17 at 15:29
@JASSY - in which case you're reliant on the format of whoever entered the data at Companies House... and because they always uppercase surnames that's what you'd have to use, well, unless you're prepared to build a lot of rules to deal with things like `MCDONALD`, `MACDONALD`, `O'SHEA`, `VAN DER VEN` or `BARRINGTON-SMYTHE` ... and it would be virtually impossible not to miss some or not to have any conflicting rules (e.g. `MACDONALD > MacDonald` but `MACINTOSH > Macintosh`) — CD001, Jul 25 '17 at 15:40

RealCheeseLord · Answer 1 · 2017-07-26T08:43:19.583

SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName

from MYSQL Website

so for your Problem:

SELECT CASE WHEN LENGTH(first_name) = 2 OR (LENGTH(first_name) = 3 AND first_name LIKE '% %')  -- check if Initials as first name
            THEN CONCAT(UPPER(REPLACE(first_name, " ", "")),                                   -- initials in UPPER, no spaces
                        " ", 
                        UPPER(SUBSTRING(last_name, 1, 1)),                                    -- First letter last Name
                        LOWER(SUBSTRING(last_name FROM 2)))                                   -- Rest last Name
            ELSE (CASE WHEN first_name LIKE '% %'                                              -- check if first_name contains space
                       THEN CONCAT(UPPER(SUBSTRING(first_name, 1, 1)),
                                   LOWER(SUBSTRING(first_name, 2, INSTR(first_name, " ")-2)),
                                   " ",
                                   UPPER(SUBSTRING(first_name, INSTR(first_name, " ")+1, 1)),
                                   LOWER(SUBSTRING(first_name FROM INSTR(first_name, " ")+2)),  
                                   " ",                                                         -- Space between first and last Name
                                   UPPER(SUBSTRING(last_name, 1, 1)),                           -- First letter last Name
                                   LOWER(SUBSTRING(last_name FROM 2)))                          -- Rest last Name
                       ELSE CONCAT(UPPER(SUBSTRING(first_name, 1, 1)),                          -- First letter first Name
                                   LOWER(SUBSTRING(first_name FROM 2)),                         -- Rest first Name
                                   " ",                                                         -- Space between first and last Name
                                   UPPER(SUBSTRING(last_name, 1, 1)),                           -- First letter last Name
                                   LOWER(SUBSTRING(last_name FROM 2))) END)END AS properName    -- Rest last Name

not very pretty though

Unfortunately this won't work on the "Jane Louise" example - the statement returns "Jane louise" — JASSY, Jul 25 '17 at 15:01
yeah, i thought so but didn't want to dive into it, I edited, but am unable to actually try it, so Feedback would be appreciated — RealCheeseLord, Jul 26 '17 at 08:01

Capitalise Name and Initials in a String

1 Answers1