Script to Create a ProperCase Function

--**********************************************************
-- Author: Sean Boate
-- Date: 08/07/2003
-- Last Modified: 10/24/2003
--
-- Type: Recursive Function
--
-- Tested On: SQL 2000
--
-- Purpose: To create a User Defined SQL Function that can
--	convert a string into a Proper Case string.
--
-- Usage: TransactSQL
-- Ex.:   {dbname}.{owner_name}.ProperCase('some_string')
--
--	  Select {dbname}.dbo.ProperCase('CITY')
--	  returns 'City'
--
--	  Select {dbname}.dbo.ProperCase('CITY STATE')
--	  returns 'City State'
--
-- Notes:
-- The first CREATE FUNCTION is so that the system has a
-- function to work with in the second ALTER FUNCTION since
-- SQL does not like Recursive Function Calls in its Create
-- Routine.
--
-- Last Modified:
-- To handle strings with spaces in the first and/or last
-- positions.
-- 
--**********************************************************

CREATE FUNCTION ProperCase
	(@STRING AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @tstring VARCHAR(8000)
	If @STRING is NULL
	BEGIN
		SELECT @tstring = NULL
	END
	ELSE
	BEGIN
		SELECT @tstring = UPPER(Left(RTRIM(LTRIM(@STRING)),1)) + LOWER(RIGHT(RTRIM(LTRIM(@STRING)),LEN(RTRIM(LTRIM(@STRING)))-1))
	END
	RETURN @tstring
END
GO

--**********************************************************
ALTER   FUNCTION ProperCase
	(@STRING AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @tstring VARCHAR(8000)
	DECLARE @trimmed VARCHAR(8000)
	DECLARE @index INT

	SELECT @trimmed = RTRIM(LTRIM(@STRING))

	If @trimmed is NULL
	BEGIN
		SELECT @tstring = NULL
	END
	ELSE
	BEGIN
		SELECT @index = CHARINDEX(' ',@trimmed)
		IF @index = 0
		BEGIN
			SELECT @tstring = UPPER(Left(RTRIM(LTRIM(@trimmed)),1)) + LOWER(RIGHT(RTRIM(LTRIM(@trimmed)),LEN(RTRIM(LTRIM(@trimmed)))-1))
		END
		ELSE
		BEGIN
			SELECT @tstring = dbo.ProperCase(RTRIM(LTRIM(Left(@trimmed,@index - 1)))) + ' ' + dbo.ProperCase(RTRIM(LTRIM(Right(@trimmed,LEN(@trimmed) - @index))))
		END
	END
	RETURN @tstring
END
GO
--**********************************************************