T-SQL Query Problem

Question

I have a column in Database called Full Name and I want split that name as FirstName and LastName:

Here is an Example:

FullName Sam Peter

I want this to be

FirstName   LastName
-------------------- 
Sam          Peter

But the Problem is Some of the columns in Database have Full Names Like this

FullName
--------
Sam George Jack Peter
Sam Adam Peter

I want this to be

FirstName           LastName
---------           --------
Sam George Jack     Peter
Sam Adam            Peter

How do I write T-SQL Query for this.

Thanks in Advance for all the help

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

There's a very thorough name parsing routine described in this answer. It handles your situation, along with much trickier cases like "Mr. Martin J Van Buren III".

edited May 23 '17 at 12:11

Community

1
1

answered Dec 10 '10 at 19:50

Ken Redler

23,863
8
57
69

score 0 · Answer 2 · answered Dec 10 '10 at 19:39

0

String manipulation in SQL Server is notoriously weak.

Your best bet is to do it in your application layer.

For your example with more than 2 names, how do you know which fields those additional names go into? Are you guaranteed they will always have only one last name?

answered Dec 10 '10 at 19:39

JNK

63,321
15
122
138

In my Case I know that it has only one last name – Sam Dec 10 '10 at 19:45

score 0 · Answer 3 · answered Dec 10 '10 at 19:44

0

Found this on net (did not test it)

REVERSE(SUBSTRING(REVERSE([FullName]),1,FINDSTRING(REVERSE([FullName])," ",1)))

You can test it with

SELECT REVERSE(SUBSTRING(REVERSE([FullName]),1,FINDSTRING(REVERSE([FullName])," ",1)))
FROM Table

if it works you can then

UPDATE Table
SET LastName = REVERSE(SUBSTRING(REVERSE([FullName]),1,FINDSTRING(REVERSE([FullName])," ",1)))

I leave the exercise for the first name to you.

answered Dec 10 '10 at 19:44

Unreason

12,556
2
34
50

Not Working. But Thanks for your help – Sam Dec 10 '10 at 20:04

Alex · Answer 4 · 2010-12-10T20:54:32.810

0

Are you just splitting at the last space? If so this should work:

select 'Sam George Jack Peter' as FullName
into #names
union select 'Sam Adam Peter'

select LEFT(FullName,LEN(FullName)-CHARINDEX(' ',REVERSE(FullName))) as FirstName
  ,RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName))-1) as LastName
from #names

EDIT: To handle names with no spaces and put the FullName as LastName

select 'Sam George Jack Peter' as FullName
into #names
union select 'Sam Adam Peter'
union select 'Peter'

select  CASE 
            WHEN    CHARINDEX(' ',FullName) = 0 THEN ''
            ELSE    LEFT(FullName,LEN(FullName)-CHARINDEX(' ',REVERSE(FullName)))
        END as FirstName
        ,CASE 
            WHEN    CHARINDEX(' ',FullName) = 0 THEN FullName
            ELSE    LTRIM(RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName))))
        END as LastName
from #names

edited Dec 10 '10 at 20:54

answered Dec 10 '10 at 19:48

Alex

353
1
6

But I have so many Columns like this So Do I need to write select for each record? – Sam Dec 10 '10 at 19:50
It is giving me following error:Msg 536, Level 16, State 2, Line 1 Invalid length parameter passed to the RIGHT function. – Sam Dec 10 '10 at 19:58
1

@Sam, once you get the syntax right you can write user defined function and user that http://www.databasejournal.com/features/mssql/article.php/3348181/User-Defined-Functions-in-Microsoft-SQL-Server-2000.htm – Unreason Dec 10 '10 at 20:43
@Sam, You probably have some full names that have no spaces in them thus you are ending up with RIGHT(FullName,-1) which is invalid. Edited the response the handle this. – Alex Dec 10 '10 at 20:53

T-SQL Query Problem

4 Answers4