SQL Server: TitleCase function

For those of you switching from Oracle to SQL server, one function that is missing is the InitCap that makes the first letter of every word capitalized. It's a handy function to have when cleaning up data.

I have an SQL book that gives the following function that does the same thing.

CREATE FUNCTION TitleCase (@StrIn NVARCHAR(1024))
RETURNS NVARCHAR(1024)
AS
  BEGIN
    DECLARE
      @StrOut NVARCHAR(1024),
      @CurrentPosition INT,
      @NextSpace INT,
      @CurrentWord NVARCHAR(1024),
      @StrLen INT,
      @LastWord BIT

    SET @NextSpace = 1
    SET @CurrentPosition = 1
    SET @StrOut = ''
    SET @StrLen = LEN(@StrIn)
    SET @LastWord = 0

    WHILE @LastWord = 0
      BEGIN
        SET @NextSpace = CHARINDEX(' ',RTRIM(@StrIn), @CurrentPosition)
        IF  @NextSpace = 0 -- no more spaces found
          BEGIN
            SET @NextSpace = @StrLen
            SET @LastWord = 1
          END
        SET @CurrentWord = UPPER(SUBSTRING(@StrIn, @CurrentPosition, 1))
        SET @CurrentWord = @CurrentWord +
LOWER(SUBSTRING(@StrIn, @CurrentPosition+1, @NextSpace - @CurrentPosition))
        SET @StrOut = @StrOut +@CurrentWord
        SET @CurrentPosition = @NextSpace + 1
    END
    RETURN @StrOut
  END

Applies to: TIMSS5 & TIMSS6