SELECT	[T_ID] = IDENTITY(int, 1, 1),
	[FullName]
INTO	#TEMP
FROM
(
	SELECT	[FullName] = 'A. Smith Jr'
	UNION ALL SELECT 'Able Smith Jr'
	UNION ALL SELECT 'Able X Smith Jr'
	UNION ALL SELECT 'Able X. Smith Jr'
	UNION ALL SELECT 'Able X Y Z Smith Jr'
	UNION ALL SELECT 'Able X Y Z Smith Junior'
) AS T

SELECT	FullName,
	[FirstName] = LEFT(FullName, Offset),
	[LastName] = LTrim(STUFF(FullName, 1, Offset+1, ''))
FROM	#TEMP AS U
	JOIN
	(
		SELECT	T_ID,
			[Offset] = LEN(FullName)
				- (
					CHARINDEX(' ', 
						REVERSE(
				        		LEFT(FullName,
								LEN(FullName) - CHARINDEX(' ', REVERSE(FullName))
							)
						)
					)
					+ CHARINDEX(' ', REVERSE(FullName))
				)
		FROM	#TEMP
		WHERE	1=1
			AND FullName LIKE '% % %'	-- Rows with at least 2 spaces
			AND	-- temporary code to select suitable "suffixes"
			(
				   FullName LIKE '% Jr'
				OR FullName LIKE '% Junior'
			)
	) AS T
		ON T.T_ID = U.T_ID
GO
DROP TABLE #TEMP
GO

select 
	fullname,
	substring(first_part,1,len(first_part)-charindex(' ',reverse(first_part))) as firstname,
	right(first_part,charindex(' ',reverse(first_part))) + ' '+last_part as latname 
from
	(
	select 
		fullname,
		substring(fullname,1,len(fullname)-charindex(' ',reverse(fullname))-1) first_part,
		right(fullname,charindex(' ',reverse(fullname))-1) as last_part from #temp 
	where 
		FullName LIKE '% % %'	-- Rows with at least 2 spaces
				AND	-- temporary code to select suitable "suffixes"
				(
					   FullName LIKE '% Jr'
					OR FullName LIKE '% Junior'
				)
) as t

quote:

---

Originally posted by madhivanan

Will this perform better?

---