Convert the first character of each word to upper case

CREATE Function init_caps

(

@pi_string varchar(max)

)

RETURNS varchar(max)

AS

BEGIN

DECLARE

@string varchar(max)

,@formatted_string varchar(max)

,@length int

SET @string = ltrim(rtrim(@pi_string))

SET @length = len(@string)

SET @formatted_string = upper(left(@string,1)) + lower(substring(@string,2,@length))

-- change first character after each space to upper

DECLARE @space int

SET @space = charindex(' ', @string)

WHILE @space > 0

BEGIN

SET @formatted_string = left(@formatted_string,@space)

+ upper(substring(@formatted_string,@space+1, 1))

+ substring(@formatted_string,@space+2, 2000)

SET @space = charindex(' ', @string, @space+1)

END

RETURN @formatted_string

END

GO