What’s the best way to capitalise the first letter of each word in a string in SQL Server

Question

What’s the best way to capitalise the first letter of each word in a string in SQL Server.

Answer 1

From http://www.sql-server-helper.com/functions/initcap.aspx

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )  RETURNS VARCHAR(4000) AS BEGIN  DECLARE @Index          INT DECLARE @Char           CHAR(1) DECLARE @PrevChar       CHAR(1) DECLARE @OutputString   VARCHAR(255)  SET @OutputString = LOWER(@InputString) SET @Index = 1  WHILE @Index <= LEN(@InputString) BEGIN     SET @Char     = SUBSTRING(@InputString, @Index, 1)     SET @PrevChar = CASE WHEN @Index = 1 THEN ' '                          ELSE SUBSTRING(@InputString, @Index - 1, 1)                     END      IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')     BEGIN         IF @PrevChar != '''' OR UPPER(@Char) != 'S'             SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))     END      SET @Index = @Index + 1 END  RETURN @OutputString  END GO 

There is a simpler/smaller one here (but doesn't work if any row doesn't have spaces, "Invalid length parameter passed to the RIGHT function."):

http://www.devx.com/tips/Tip/17608