Split FullName into First and Last Name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Split FullName into First and Last Name

Author

Topic

HassanGulzar
Starting Member

4 Posts

Posted - 2010-11-03 : 08:30:55

Hello guys!

I'm in a bit of a dilemma here. here is a table structure:

OID-FirstName-LastName-FullName
2---NULL------NULL-----Ifty Khan
3---NULL------NULL-----JASON JONES
4---NULL------NULL-----Mike Hackett (pharm)

The column was being used and new requirements demand a FirstName and LastName instead of FullName. FullName stays for backward compatibility but I need to fill in the FirstName and LastName from FullName based on the space in FullName.

Any help will be greatly appreciated

Sachin.Nand

2937 Posts

Posted - 2010-11-03 : 08:34:30

Will the word before the first space always be considered as firstname?

PBUH

HassanGulzar
Starting Member

4 Posts

Posted - 2010-11-03 : 08:38:24

Yup. Anything until the first ' ' is encountered is a FirstName

HassanGulzar
Starting Member

4 Posts

Posted - 2010-11-03 : 08:47:33

I have this so far:

SELECT
    Manager.OID
  , Manager.FullName
  , LEFT(Manager.FullName, patindex('%[ ' + char(8) + ']%', Manager.FullName) - 1) AS FirstName  
  --, RIGHT(Manager.FullName, CHARINDEX(CHAR(8), REVERSE(Manager.FullName)) - 1) AS LastName
FROM
  Manager
  ORDER by  Manager.OID

It dies after a while once it hits a FullName without any space character like: "hassan" or "n/a"

Sachin.Nand

2937 Posts

Posted - 2010-11-03 : 08:49:16


declare @nm varchar(30)='Ifty Khan'
select substring(@nm,charindex(' ',@nm),len(@nm))FirstName,substring(@nm,1,charindex(' ',@nm))LastName

PBUH

HassanGulzar
Starting Member

4 Posts

Posted - 2010-11-03 : 09:48:21

Worked fabulously!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources