Split full name into firstname, MI 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

General SQL Server Forums

New to SQL Server Programming

Split full name into firstname, MI and last name

Author

Topic

Laks_sql
Starting Member

1 Post

Posted - 2011-09-13 : 16:41:03

Hi,

I have a need wherein I have to check if the middle initial exists and if it does i need to split the full name into firstname, mi and lastname else if mi doesnt exist, just firstname and lastname with mi returning null..

How would I do this inside of a SQL query. Am a newbie(obviously), so any response is appreciated!

Thanks in adnavce

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-13 : 16:45:14

Why would you want to do the split inside SQL? This should be done in your application. Return the raw data from SQL to your app.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

memorykills
Starting Member

18 Posts

Posted - 2011-09-13 : 17:11:13

If firstname/middlename/lastname are separated by one space only:

SELECT LEFT(Name, CHARINDEX(' ', Name)) AS FirstName,
CASE WHEN CHARINDEX(' ', Name) <> LEN(Name) - CHARINDEX(' ', REVERSE(Name)) + 1 THEN
SUBSTRING(Name, CHARINDEX(' ', Name)+ 1, LEN(Name) - CHARINDEX(' ', REVERSE(Name))-CHARINDEX(' ', Name))
end as middle,
RIGHT( Name, CHARINDEX(' ', REVERSE(Name))) AS LastName
from myTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 23:40:30

another way is to use PARSENAME like


SELECT COALESCE(PARSENAME(REPLACE(Name,' ','.'),3),PARSENAME(REPLACE(Name,' ','.'),2),PARSENAME(REPLACE(Name,' ','.'),1))  AS FirstName,
CASE WHEN PARSENAME(REPLACE(Name,' ','.'),3) IS NOT NULL THEN PARSENAME(REPLACE(Name,' ','.'),2) ELSE NULL END AS MiddleName, 
CASE WHEN PARSENAME(REPLACE(Name,' ','.'),2) IS NOT NULL THEN  PARSENAME(REPLACE(Name,' ','.'),1) ELSE NULL END AS LastName
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 03:00:35

Is the data really just "firstname, mi, lastname" or "firstname, lastname"?

What about "Aaaa b van Zzzz" and double-barrelled names which are un-hyphenated like "Aaaa B Yyyy Zzzz" and firstnames like "Anne Marie"?

If you reliably have commas, and either one or two, then splitting is straightforward using either method above (but matching "," rather than " "). Visakh's method may fail if there is a space before the comma, or a trailing space




SELECT	[Name],
	'MemKills:',
	LTrim(RTrim(COALESCE(PARSENAME(REPLACE(Name,',','.'),3),PARSENAME(REPLACE(Name,',','.'),2),PARSENAME(REPLACE(Name,',','.'),1)))) AS FirstName,
	LTrim(RTrim(CASE WHEN PARSENAME(REPLACE(Name,',','.'),3) IS NOT NULL THEN PARSENAME(REPLACE(Name,',','.'),2) ELSE NULL END)) AS MiddleName, 
	LTrim(RTrim(CASE WHEN PARSENAME(REPLACE(Name,',','.'),2) IS NOT NULL THEN  PARSENAME(REPLACE(Name,',','.'),1) ELSE NULL END)) AS LastName,
	'Visakh:',
	LTrim(RTrim(LEFT(Name, CHARINDEX(',', Name)-1))) AS FirstName,
	LTrim(RTrim(CASE WHEN CHARINDEX(',', Name) <> LEN(Name) - CHARINDEX(',', REVERSE(Name)) + 1
		THEN SUBSTRING(Name, CHARINDEX(',', Name)+ 1, LEN(Name) - CHARINDEX(',', REVERSE(Name))-CHARINDEX(',', Name))
		END)) AS MiddleName,
	LTrim(RTrim(RIGHT(Name, CHARINDEX(',', REVERSE(Name))-1))) AS LastName
FROM 
(
	SELECT	[Name] = 'A, Zzz' UNION ALL
	SELECT	'Aaaa, Zzz' UNION ALL
	SELECT	'A, B, Zzz' UNION ALL
	SELECT	'Aaaa, B, Zzz' UNION ALL
	SELECT	'Aaaa, Bbbb, Zzz' UNION ALL
	SELECT	'Aaaa, B, de Zzz' UNION ALL
	SELECT	'Aaaa, B, van Zzz' UNION ALL
	SELECT	'Aaaa, B, Yyy-Zzz' UNION ALL
	SELECT	'Aaaa, B, d''Zzz' UNION ALL
	SELECT	'Aaaa Bbbb, C, Zzz' UNION ALL
	SELECT	'Aaaa Bbbb, Cccc, Zzz'
) AS X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources