first letters in firstname and lastname uppercase

Question

Assuming I have a table full of names.

firstname.lastname in a single cell.

How can I seperate these into "Firstname Lastname", with uppercase for the first letters? Using TSQL

Sample:

mike.mikeson -> Mike Mikeson

katy.lumberjack -> Katy Lumberjack

Can we trust the format? Is it always firstname, followed by a single space, followed by lastname? — Mureinik, Oct 18 '16 at 12:34
Add some sample table data, and the expected result. Include Conan O'Brien, Ronald McDonald and other exotic names. — jarlh, Oct 18 '16 at 12:35
The case use http://stackoverflow.com/questions/230138/sql-server-make-all-upper-case-to-proper-case-title-case — Mark Schultheiss, Oct 18 '16 at 12:37
another option on title case https://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase.aspx — Mark Schultheiss, Oct 18 '16 at 12:40
It's pretty much impossible. How are you going to determine that "Vincent van Gogh" his last name is "van Gogh" and "Samuel L Jackson" his last name is "Jackson"? There is no real pattern. — Ivar, Oct 18 '16 at 12:41
@Ivar The names I use only have the format showed in the sample — MrProgram, Oct 18 '16 at 12:42
@MrProgram If that is the case, my answer below will work without issue. — iamdave, Oct 18 '16 at 12:44
This was trendy a few years back, but it can never work consistently for all name types, there is a name Mackie and a name MacKie for example - how about names like Giscard d'Estang - yes I read the article by a journalist called Mackie — Cato, Oct 18 '16 at 13:10

score 3 · Accepted Answer · answered Oct 18 '16 at 12:50

One of those times we can use the ParseName function for our benefit ;-)

SELECT original_value
     , forename
     , surname
     , Upper(SubString(forename, 1, 1)) + Lower(Substring(forename, 2, 8000)) AS formatted_forename
     , Upper(SubString(surname , 1, 1)) + Lower(Substring(surname , 2, 8000)) AS formatted_surname
FROM   (
        SELECT name AS original_value
             , ParseName(name, 2) AS forename
             , ParseName(name, 1) AS surname
        FROM   (
                VALUES ('mike.mikeson')
                     , ('katy.lumberjack')
               ) AS users (name)
       ) AS step1

score 1 · Answer 2 · answered Oct 18 '16 at 12:39

The below will answer you question as is but as comments have pointed out, you may need to also take into account names that have more than one uppercase letter in either part, such as Mary-Anne McDonald, or those that simply don't conform to your convention.

declare @a table (Name nvarchar(50))
insert into @a values
 ('fred.bloggs')
 ,('john.doe')
 ,('alan.smith')

select Name
    ,upper(left(Name,1))
        + substring(Name,2,charindex('.',Name,1)-2)
        + ' '
        + upper(substring(Name,charindex('.',Name,1)+1,1))
        + right(Name,len(Name) - charindex('.',Name,1)-1)
        as FormattedName
from @a

Kannan Kandasamy · Answer 3 · 2016-10-18T13:06:00.207

0

You can try using concat and substring for this as below

declare @name varchar(50) = 'firstname.lastname'

select case when charindex('.',@name) > 0 then  concat(upper(left(@name,1)), substring(@name,2,charindex('.',@name)-2), ' ', upper(substring(@name,charindex('.',@name)+1,1)), substring(@name,charindex('.',@name)+2, len(@name)))
            else concat(upper(left(@name,1)), substring(@name,2,len(@name))) end

edited Oct 18 '16 at 13:06

answered Oct 18 '16 at 12:49

Kannan Kandasamy

13,405
3
25
38

first letters in firstname and lastname uppercase

3 Answers3