ALTER function [dbo].[ProperCase]
(
@inputString varchar(max)
) RETURNS varchar(max) As BEGIN
--declare @inputString varchar(max)
--set @inputString = 'Geraldine Williams x'
-- Declarations -------------------------------------------------------------------------------
declare @oldlen int
DECLARE @Result varchar(2000)
set @oldlen = -1
SET @Result = ''
-- Clean up input data ------------------------------------------------------------------------
SET @inputString = lower(ltrim(rtrim(COALESCE(@inputString, ''))))
while @oldlen <> len(@inputstring) begin
set @oldlen = len(@inputstring)
set @inputstring = replace(@inputstring, ' ', ' ')
end
-- Loop through the String --------------------------------------------------------------------
SET @inputString = @inputString + ' '
WHILE 1=1 BEGIN
-- If no more spaces, exit loop
IF PATINDEX('% %',@inputString) = 0 BREAK
-- Special handing for two-letter words
IF LEN(@inputString) = 2 OR CHARINDEX(' ', @inputString) = 3 BEGIN
SET @Result = @Result
+ UPPER(LEFT(@inputString, 2))
+ SUBSTRING(@inputString, 3, CHARINDEX(' ', @inputString) - 2)
SET @inputString = SUBSTRING(@inputString, CHARINDEX(' ', @inputString) + 1, LEN(@inputString))
END
-- normal processing: capitalize the character after the first space ----------------------
ELSE BEGIN
SET @Result = @Result
+ UPPER(LEFT(@inputString, 1))
+ SUBSTRING(@inputString, 2, CHARINDEX(' ', @inputString) - 1)
SET @inputString = SUBSTRING(@inputString, CHARINDEX(' ', @inputString) + 1, LEN(@inputString))
END
END
SET @Result = substring(@Result, 1, LEN(@Result))
-- Special Handling for Prefixes --------------------------------------------------------------
set @Result = case
when @Result like 'van %'
then lower(substring(@Result,1,4)) +
upper(substring(@Result,5,1)) +
substring(@Result,6,len(@Result))
when @Result like 'Mac%'
then substring(@Result,1,3) +
upper(substring(@Result,4,1)) +
substring(@Result,5,len(@Result))
when @Result like 'Mc%'
then substring(@Result,1,2) +
upper(substring(@Result,3,1)) +
substring(@Result,4,len(@Result))
when @Result like 'Mc%' or @Result like 'O''%'
then substring(@Result,1,2) +
upper(substring(@Result,3,1)) +
substring(@Result,4,len(@Result))
else @Result
end
-- Special handing for hyphenated names -------------------------------------------------------
set @Result = case
when @Result like '%-%'
then substring(@Result,1,charindex('-',@Result)) +
upper(substring(@Result,charindex('-',@Result)+1,1)) +
substring(@Result,charindex('-',@Result)+2,len(@Result))
else @Result
end
--------------------------------------------
--select Result = @Result
RETURN @Result
END