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
--**********************************************************