SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Splitting penultimate sub-string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/29/2013 :  03:24:56  Show Profile  Reply with Quote
I'm splitting some names. I have everything up to and including

Mr Joe X Bloggs Jr MBA, BSc

I need:

Title,
First,
Last,
Qualifications

I'm sorted on the whole job, except keeping the "Jr" with the surname.

I get to a point where I have the "name" as 'Joe X Bloggs Jr' and I want to extract the last two names into the "last name", thus leaving me 1..n first names.

Is there a more efficient way to do this?

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

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 12/03/2013 :  08:25:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Will this perform better?

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


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 12/03/2013 08:27:10
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 12/05/2013 :  12:22:21  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Will this perform better?


Thanks Madhi. Looks better ... maybe?.

Mine:

Table 'Worktable'. Scan count 0, logical reads 0
Table '#TEMP'. Scan count 2, logical reads 4

Yours:

Table '#TEMP'. Scan count 1, logical reads 2

but on a large section of data:

Mine:
-- Table 'Worktable'. Scan count 0, logical reads 0
-- Table '#KBM_TEMP'. Scan count 2, logical reads 7472

Yours:
-- Table 'Worktable'. Scan count 0, logical reads 0
-- Table '#KBM_TEMP'. Scan count 2, logical reads 7472

Might be that, although my sample size was quite large, the number of actual rows to be processed was small, but I've kept the code to try next time I have a larger active sample size

Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000