CODEWITHSUNDEEP
sqlsql-servert-sql
Emarie
Emarie

Reputation: 25

Splitting FullName in SQL

I am trying to split Fullname with this sql code that I got from someone here in Stack Overflow however it won't work because the format of the Fullname is like this "LastName, FirstName Middle Initial" and there are names that don't have middle initials.

select left(m.fullname, CHARINDEX(' ', m.fullname)) as [Last Name]
      ,substring(m.fullname, CHARINDEX(' ', m.fullname)+1
      ,len(m.fullname)-(CHARINDEX(' ', m.fullname)-1)) as [First Name]
From Temp

Can someone please let me know how to query this. The example format will be like this:

FullName: Smith, John D

Desired Output:

LastName    FirstName
Smith        John

Thank you in advance

Upvotes: 2

Views: 326

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

In addition to The Impaler's answer here's an answer how you might split your FullName. This will not solve the very well explained issues with naming rules, but - at least - you get hands on each part in a strucutured format:

DECLARE @mockup TABLE(ID INT IDENTITY, FullName NVARCHAR(500));
INSERT INTO @mockup VALUES(N'Smith, John D')
                         ,(N'Parker, Dr. James');

SELECT CAST(N'<x><y>' 
              + REPLACE(
                  REPLACE(
                            (SELECT FullName AS [*] FOR XML PATH(''))
                           ,N','
                           ,N'</y></x><x><y>'
                         )
                         ,N' '
                         ,N'</y><y>') 
              + N'</y></x>' AS XML)
              .query(
                      N'
                        for $x in /x
                        return 
                        <x>
                        {
                            for $y in $x/y[text()]
                            return $y
                        }
                        </x>
                        '
                   )
FROM @mockup;

The result for the first name

<x>
  <y>Smith</y>
</x>
<x>
  <y>John</y>
  <y>D</y>
</x>

And for the second

<x>
  <y>Parker</y>
</x>
<x>
  <y>Dr.</y>
  <y>James</y>
</x>

You see, that the comma separated parts are found within <x> while the blank separated parts are found as nested <y>.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48865

Parsing a "full name" into first, middle, and last name is not straightforward in a database. Don't try it unless this is just a homework that considers simplified non-real life cases.

Parsing a full name requires non trivial logic to recognize all kinds of cases. There are several reasons for it:

  • Most languages have multi word first and last names. They are not hyphenated as in the US but they include bona fide multiple word each. Even some US (very few) names have multiple words.
  • Some people have middle name or none at all.
  • Some people include suffixes as part of their name:
    • "Jr", "Sr" are patronimic ones.
    • "PhD", "Esquire" are title ones.
    • There are more complicated cases that convey different kind of information.
  • In some languages first and last names are inverted. Korean for example.
  • You need to consider that names can have special characters: "O'Hara" for example. Pretty much anything can be valid, depending on the language.
  • You need to consider using unicode, to recognize alphabetic symbols correctly. French anyone? Russian? Mandarin?
  • Finally, you need to consider a myriad of special cases. That is, you need a knowledge base to resolve all kinds of situations, that won't be covered by the "normal" rules you'll use.

Of course, all of the above can be done using SQL. However, SQL is not the right option IMHO.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use parsename() function :

select fullname, parsename(replace(fullname, ' ', '.'), 3) as lastname,
                 parsename(replace(fullname, ' ', '.'), 2) as firstname
from table t;

Upvotes: 1

Diego Victor de Jesus
Diego Victor de Jesus

Reputation: 3023

Split your string using using the comma. The result should be an array. Index zero will be the last name, and index one will be the first name.

Upvotes: 1

Related Questions