Proper Case

MS SQL server has built-in functions to convert a string to uppercase or lowercase.
What about proper case / title case?
Example: Convert DURBAN – SOUTH AFRICA to Durban – South Africa

Below is a function that can do this.

CREATE FUNCTION udf_ProperCase (@x varchar(7999))
RETURNS varchar(7999)
AS
BEGIN
  DECLARE @y int
  SET @y = 1

  SELECT @x = UPPER(SUBSTRING(@x, 1, 1)) + LOWER(SUBSTRING(@x, 2, LEN(@x) - 1)) + ' '

  WHILE @y < LEN(@x)
  BEGIN
    SELECT @y = CHARINDEX(' ', @x, @y)
    SELECT @x = SUBSTRING(@x, 1, @y) + UPPER(SUBSTRING(@x, @y + 1, 1)) + SUBSTRING(@x, @y + 2, LEN(@x) - @y + 1)	
    SELECT @y = @y + 1
  END
  RETURN @x
END

The example below shows you how to use this function.

SELECT dbo.udf_ProperCase('THE QUICK BROWN FOX JUMPED OVER THE LAZY DOG')

Result: The Quick Brown Fox Jumped Over The Lazy Dog

I hope that this article helps someone out there.
If you are someone like me that prefers proper / title case, then this function is for you.
Feel free to comment on this article.