First letter change to uppercase with MySQL SELECT

Question

This question shows me how to create a function and then UPDATE a table making a column first letter uppercase which is pretty involved? I simply want to output results where FirstName and LastName capitalize the first letter. Right now they are a mixture of all uppercase, lowercase or correctly formatted.

SELECT FirstName, LastName, r.Email, min(ListingID) as ListingID, ListingFirmID
    FROM sancap_Residential_Property e 
    JOIN sancap_Agent r ON e.ListingAgentID = r.AgentID
    JOIN sancap_Office d ON e.ListingFirmID = d.firmID
    WHERE ListingFirmID != 'BrokerC'
GROUP BY FirstName, LastName, r.Email, ListingFirmID

Ronak Shah · Answer 1 · 2014-09-18T11:23:13.503

1

try this:

SELECT concat(UPPER(SUBSTRING(trim(FirstName),1,1)),lower(SUBSTRING(trim(FirstName),2))) as FirstName, concat(upper(substring(trim(LastName),1,1)),lower(substring(trim(LastName),2))) as LastName, r.Email, min(ListingID) as ListingID, ListingFirmID
    FROM sancap_Residential_Property e 
    JOIN sancap_Agent r ON e.ListingAgentID = r.AgentID
    JOIN sancap_Office d ON e.ListingFirmID = d.firmID
    WHERE ListingFirmID != 'BrokerC'
GROUP BY FirstName, LastName, r.Email, ListingFirmID

edited Sep 18 '14 at 11:23

answered Sep 18 '14 at 11:06

Ronak Shah

1,539
2
13
20

Strange...the altered query totally ignores the case issue and produces the same result as the original query? – Rocco The Taco Sep 18 '14 at 11:12
That's not possible. try this SELECT concat(UPPER(SUBSTRING('rocco',1,1)),SUBSTRING('rocco',2)) as FirstName – Ronak Shah Sep 18 '14 at 11:16
That worked! Why does the data from the table not work? – Rocco The Taco Sep 18 '14 at 11:18
It should work. please load your sample data and table to sqlfiddle so I can check – Ronak Shah Sep 18 '14 at 11:20
Is there a hidden/blank character before the first character in the names? – Giles Sep 18 '14 at 11:21
Oh yes that's possible, just trim your data and remove non printable character if any – Ronak Shah Sep 18 '14 at 11:22
I have updated answer that trim data before making upper case – Ronak Shah Sep 18 '14 at 11:23
Same result with the trim. The current data is a mess of uppercase, lowercase and mixed case...would that matter? – Rocco The Taco Sep 18 '14 at 11:24
No that doesn't matter. It looks like there must be hidden non printable character in starting – Ronak Shah Sep 18 '14 at 11:25
So I think you want only first character as upper case and other in lower. I have updated answer . just remove non printable character and try above query – Ronak Shah Sep 18 '14 at 11:29
check this link to remove nonprintable character:http://stackoverflow.com/questions/11535350/remove-all-or-particular-non-printable-character-from-column-in-mysql – Ronak Shah Sep 18 '14 at 11:30

Giles · Answer 2 · 2014-09-18T11:12:24.880

0

SELECT CONCAT(UPPER(LEFT(FirstName,1)), RIGHT(FirstName,LENGTH(FirstName) - 1)), CONCAT(UPPER(LEFT(LastName,1)), RIGHT(LastName,LENGTH(LastName) - 1)), r.Email, min(ListingID) as ListingID, ListingFirmID
    FROM sancap_Residential_Property e 
    JOIN sancap_Agent r ON e.ListingAgentID = r.AgentID
    JOIN sancap_Office d ON e.ListingFirmID = d.firmID
    WHERE ListingFirmID != 'BrokerC'
GROUP BY FirstName, LastName, r.Email, ListingFirmID

edited Sep 18 '14 at 11:12

answered Sep 18 '14 at 11:05

Giles

1,597
11
15

Getting this error? [Err] 1305 - FUNCTION databasel.LEN does not exist – Rocco The Taco Sep 18 '14 at 11:11
1

Actually I like Ronak Shah solution better. – Giles Sep 18 '14 at 11:13
That executes, but both you and Ronak solutions display the data in the same format as the original query. – Rocco The Taco Sep 18 '14 at 11:15

First letter change to uppercase with MySQL SELECT

2 Answers2