Sperate the Names From the Field SQL

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

General SQL Server Forums

New to SQL Server Programming

Sperate the Names From the Field SQL

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2011-06-13 : 15:30:44

Hi,

I have a string column Name - which consist of first name,middle name last name seperated by spaces. I want to separate them using the sql :

I tried the below substring function but it gives me error....is there a way I can separate the first name,middle name,last name..

The feeds can be :
John Ray
Patrick J Cotter
Fisher

substring(RTRIM(REPLACE(A.[Name],' ',' ')),1,'')

Please advice..

Thanks,

Dp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-13 : 15:49:04

You can use:

SELECT 
	PARSENAME(REPLACE(namecol,' ','.'),3) FirstName,
	PARSENAME(REPLACE(namecol,' ','.'),2) MiddleInitial,
	PARSENAME(REPLACE(namecol,' ','.'),1) LastName
FROM
	yourTable

But, this is fraught with peril. For example, you had any of the following people in your database, this would not work.

1. JJ Astor, full name:		John Jacob Astor IV
2. Prince William, full name:	William Arthur Philip Louis
3. Pablo Picasso, full name:	Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

dim
Yak Posting Veteran

57 Posts

Posted - 2011-06-13 : 16:40:13

Thanks it worked !!!

Dp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources