--**************************************
--     
-- Name: Proper Case All Words - UDF
-- Description:Proper Case All words in 
--     a string of words, and removes any extra
--      spaces. A UDF in SQL 2000 but can be ch
--     anged to a Stored Procedure in other SQL
--      versions.
This was resubmitted. I tried TO edit my previous submission AND the site deleted it.
A USER pointed out that IF a 1 charater string was sumitted the next word would NOT be capitalized. This submission fixes that problem.
-- By: Tim Cook (Intellisoft)
--
-- Inputs:String you want to Proper Case
--     . example: "THIS entry NEEDSto beProperC
--     ASE"
--
-- Returns:String formatted to Proper Ca
--     se. example: "This Entry Needs To Be Pro
--     per Case"
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=780&lngWId;=5--for details.--**************************************
--     

CREATE FUNCTION fn_PROPERCASE_All
(
--The string to be converted to proper c
--     ase
@input varchar(50)
)
--This function returns the proper case 
--     string of varchar type
RETURNS varchar(50)
AS
    BEGIN
    	IF @input IS NULL 
    	BEGIN
    		--Just RETURN NULL IF input string IS NULL
    		RETURN NULL
    	END
    	DECLARE @output varchar(50)
    	--Integer variable declarations
    	DECLARE @ctr int, @len int, @NewWord int, @White int
    	--Constant declarations
    	DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @WHITE_SPACE char
    	--Variable/Constant initializations
    	SET @ctr = 1
    	SET @NewWord = 1
    	SET @len = LEN(@Input)
    	SET @output = ''
    	SET @LOWER_CASE_a = 97
    	SET @LOWER_CASE_z = 122
    	SET @WHITE_SPACE = ' '
    WHILE @ctr <= @len
        BEGIN
        	--This loop will Remove reccuring white spaces
        SET @White = 1
        	WHILE SUBSTRING(@input,@ctr,1) = @WHITE_SPACE
            BEGIN
             IF @White = 1
             	BEGIN
             		 SET @output = @output + SUBSTRING(@input,@ctr,1)
             		 SET @ctr = @ctr + 1
            	 SET @White = @White + 1
             	END
             ELSE
                BEGIN
                SET @output = @output + SUBSTRING(@input,@ctr,0)
                 		SET @ctr = @ctr + 1
            END

             	END
            	IF @NewWord = 1
            	BEGIN
            	--Converting the first character TO upper CASE
            	SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
            SET @ctr = @ctr + 1
            	IF SUBSTRING(@input,@ctr,1) = @WHITE_SPACE BEGIN -- Added FOR 1 Char word
            		SET @NewWord = 1
            	END	
            	ELSE BEGIN
            		SET @NewWord = 0
            	END
            	END
            	ELSE
            	BEGIN
            	WHILE SUBSTRING(@input,@ctr,1) != @WHITE_SPACE
             	 IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
             		BEGIN
            		SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))	
             		SET @ctr = @ctr + 1
             		END
             		ELSE
             		BEGIN
            SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
            SET @ctr = @ctr + 1 
             	END
             SET @NewWord = 1
            	END
        END

        RETURN @Output
    END