Proper Case 2

[sparkbyte]

I found this some time ago and just rediscovered it again and have seen this question asked multiple times.

This is one of the proper case functions that I like to use because of it's ability to handle names like

Robert McNamara and Ewan MacIntosh McGregor, DDS, LLP

even with suffixes like DDS,LLP and the like. It is also easily modified to include additional exceptions.

The code example is setup to use the adventureworks DB.

use adventureworks
go
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create   FUNCTION fn_ProperCase (@Str varchar(max)) returns varchar(max) as 

BEGIN 
/*
Purpose: Propercase function that handles Mc and Mac surnames correctly, as well as exception list for items
to leave as lower case, upper case and exceptions to tha Mc/Mac rules.
Adding items to the three exception strings to suit your needs.

Author: Clayton_Groom@hotmail.com. 2004-03-02
Loosly based on a function by Tim Raster. Link: [URL unfurl="true"]http://www.houseoffusion.com/cf_lists/index.cfm?method=messages&ThreadID=541&forumid=6&#1810[/URL]
Added logic to handle the first or only word in a string correctly. was not handling exceptions properly. 
Added exception cases for items to be excluded and and items to keep in upper case
Changed to use patindex to search for delimiters instead of looping on each delimiter.
2004-03-04 Added numbers and additional special characters to delimiter search criteria
2004-03-05 Added code to convert html char() tag for apostrophe character, added more exceptions
2004-03-11 CDG added more delimiters and html exceptions
2004-03-19 CDG Added '&' delimiter and replace for ''' html string.
2004-06-23 CDG added a couple more replaces for html coded characters
*/


If @Str > '' 
begin 
	declare @DelimPos smallint
		,@NextDelim smallint
		,@StrLen smallint
		,@WordLen smallint
		
	declare @w_LOWERCASE_EXCEPTIONS varchar(4000)  
	declare @w_ALLCAPS_EXCEPTIONS varchar(4000)
	declare @w_MAC_EXCEPTIONS varchar(4000)  
	declare @w_MIXEDCAPS_EXCEPTIONS varchar(4000)

	set @w_LOWERCASE_EXCEPTIONS = '|with|when|and|or|an|the|from|to|on|as|of|in|at|for|will|'
	set @w_ALLCAPS_EXCEPTIONS = '|LLP|LLC|DDS|MD|JD|MC|OB|JJ|NMMA|RRT|US|USA|PO|SW|SE|NW|NE|N|E|W|S|II|III|IV|VI|VII|VIII|IX|RV|LP|RR|HC|NRA|SCUBA|NAACP|NH|CA|HCI|SS|KC|RC|MC|DL|'
	set @w_MAC_EXCEPTIONS = '|Macon|Maceo|Mackinac|Mackinac Island|Mackinaw|Mackinaw City|Macks Creek|Macks Inn|Macom|Macomb|Macombtownship|Macon|Macy|' 
	set @w_MIXEDCAPS_EXCEPTIONS = '|PhD|'

	-- clean up html tags that sometimes get into name/address data from web forms
	set @str= replace(@str, ''','''')	
	set @str= replace(@str, ''','''')	
	set @str= replace(@str, ''','''')	
	set @str= replace(@str, '&','')	
	set @str= replace(@str, '''','''')
	set @str= replace(@str, ''','''')

	-- looping logic requires the string start and end with a valid delimiter to avoid having to code for special cases
	set @str = '|' + lower(@str) + '|'

	set @Strlen = len (@Str)
	
	set @delimpos = patindex('%[ 0-9/\.''"`(){}<>+--_|#$*:;?&%]%', @str)
	set @nextDelim = @delimpos + patindex('%[ 0-9/\.''"`(){}<>+--_|#$*:;?&%]%', substring(@str, @delimpos+1, @strlen - @delimpos )) 
	set @wordlen = (@NextDelim - @DelimPos) -1
	
	while @DelimPos  < @NextDelim  
	begin
		set @Str = case 	
			-- 's condition. the 's' following a possessive case should not be upper cased...
			when substring(@str,@delimpos ,1) = ''''  
				and substring(@str, @DelimPos + 1, @WordLen) = 's' 
				and @nextDelim = @DelimPos +2
			then Left(@Str, @DelimPos) 
				+ lower(substring(@Str, @DelimPos + 1, 1)) 
				+ Right(@Str, @StrLen - @DelimPos - 1) 
			-- handle "S.A.". "A.". '|a|' is the only single characther in the lowercase exception list...
			when substring(@str, @delimpos, (@nextDelim - @DelimPos) + 1 ) = ' a '  
			then Left(@Str, @DelimPos) 
				+ lower(substring(@Str, @DelimPos + 1, 1)) 
				+ Right(@Str, @StrLen - @DelimPos - 1) 
			-- mixed caps exceptions
			when charindex('|' + substring(@Str, @DelimPos + 1, @WordLen)+ '|', @w_MIXEDCAPS_EXCEPTIONS) > 0
			then Left(@Str, @DelimPos) 
				+ substring(@w_MIXEDCAPS_EXCEPTIONS
					,charindex('|' + substring(@Str, @DelimPos + 1, @WordLen)+ '|', @w_MIXEDCAPS_EXCEPTIONS)+ 1
					, @WordLen) 
				+ substring(@Str, @DelimPos + 1 + @WordLen,  @StrLen - (@DelimPos + @WordLen))
			-- ALL caps exceptions
			when charindex('|' + substring(@Str, @DelimPos + 1, @WordLen)+ '|', @w_ALLCAPS_EXCEPTIONS) > 0
			then Left(@Str, @DelimPos) 
				+ upper(substring(@Str, @DelimPos + 1, @WordLen)) 
				+ substring(@Str, @DelimPos + 1 + @WordLen,  @StrLen - (@DelimPos + @WordLen))
			-- no-caps exceptions
			when charindex('|' + substring(@Str, @DelimPos + 1, @WordLen) + '|', @w_LOWERCASE_EXCEPTIONS) > 0
			then  Left(@Str, @DelimPos) 
				+ lower(substring(@Str, @DelimPos + 1, @WordLen)) 
				+ substring(@Str, @DelimPos + 1 + @WordLen,  @StrLen - (@DelimPos + @WordLen))
			-- Mc exceptions
			when substring(@Str, @DelimPos + 1, 2) = 'Mc' 
				and charindex('|' + substring(@Str, @DelimPos + 1, @WordLen)+ '|', @w_MAC_EXCEPTIONS) = 0 
				and @WordLen > 2 
			then   Left(@Str, @DelimPos) 
				+ upper(substring(@Str, @DelimPos + 1, 1 ))
				+ lower (substring(@Str, @DelimPos + 2, 1 ))
				+ upper(substring(@Str, @DelimPos + 3,1 ))
				+ substring(@Str, @DelimPos + 4, @WordLen -3)
				+ substring(@Str, @DelimPos + 1 + @WordLen,  @StrLen - (@DelimPos + @WordLen))
			-- Mac exceptions
			when substring(@Str, @DelimPos + 1, 3) = 'Mac' 
				and charindex('|' + substring(@Str, @DelimPos + 1, @WordLen)+'|', @w_MAC_EXCEPTIONS) = 0 
				and @WordLen > 3 
			then   Left(@Str, @DelimPos) 
				+ upper(substring(@Str, @DelimPos + 1, 1 ))
				+ lower (substring(@Str, @DelimPos + 2, 2 ))
				+ upper(substring(@Str, @DelimPos + 4,1 ))
				+ substring(@Str, @DelimPos + 5, @WordLen -4)
				+ substring(@Str, @DelimPos + 1 + @WordLen,  @StrLen - (@DelimPos + @WordLen))
			--normal case
			else Left(@Str, @DelimPos) 
				+ upper(substring(@Str, @DelimPos + 1, 1)) 
				+ Right(@Str, @StrLen - @DelimPos - 1) 
			end
		set @DelimPos = @NextDelim
		set @NextDelim = @DelimPos + patindex('%[ 0-9/\.''"`(){}<>+--_|#$*:;?&%]%', substring(@Str, @DelimPos+1, @Strlen - @DelimPos )) 
		set @wordlen = (@NextDelim - @DelimPos) -1
		While @wordlen = 0 -- skip processing delimiters as words
		begin
			set @DelimPos = @NextDelim
			set @NextDelim = @DelimPos + patindex('%[ 0-9/\.''"`(){}<>+--_|#$*:;?&%]%', substring(@Str, @DelimPos+1, @Strlen - @DelimPos )) 
			set @wordlen = (@NextDelim - @DelimPos) -1
		end
	end

end 
-- set first letter of string to upper case. handles lines beginning with an excluded preposition, and trim delimiters
set @Str = upper(substring(@Str,2,1)) + substring(@Str,3, @Strlen -3)

return @Str
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To convert the string 'william h gates' to proper case:
SELECT dbo.fn_ProperCase('william h gates')

--To convert the Notes field of titles table in pubs database to proper case:
SELECT dbo.fn_ProperCase(FirstName + ' ' + LastName) FROM adventureworks.Person.contact

set nocount on
select dbo.fn_ProperCase('macarthur')
select dbo.fn_ProperCase('Robert mcnamara and ewan macintosh mcgregor, dds, llp')
select dbo.fn_ProperCase('BOB AND RITA MCEWAN')
select dbo.fn_ProperCase('BOB And ROBERT MCCLINTOCK DDS')
select dbo.fn_ProperCase('BOB & ROBERT MCCLINTOCK-DDS')
select dbo.fn_ProperCase('Nh Coach & Camper/gilmans')
select dbo.fn_ProperCase('paul o''neal')
select dbo.fn_ProperCase('paul o''neal''s yachts')
select dbo.fn_ProperCase('in motion marine')
select dbo.fn_ProperCase('H & h Marine')
select dbo.fn_ProperCase('Endless Summer Rv''S')
select dbo.fn_ProperCase('104 brockhaven court apt 10b')
select dbo.fn_ProperCase('104 brockhaven court apt 10a room 11')
select dbo.fn_ProperCase('this is a test and only a test eh? arthur a. dent')

Thanks

John Fuhrman