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

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

From stackoverflow Dominic Godin

• 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:

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

  Sharique : what is meaning of UPPER(@Char) != 'S'? why we are using this?
  From Espo

• A great set of string manipulation functions can be found here:

  http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-1/

  From

• A variation of the one I've been using for quite some time is:

  CREATE FUNCTION [widget].[properCase](@string varchar(8000)) RETURNS varchar(8000) AS
  BEGIN   
      SET @string = LOWER(@string)
      DECLARE @i INT
      SET @i = ASCII('a')
      WHILE @i <= ASCII('z')
      BEGIN
       SET @string = REPLACE( @string, ' ' + CHAR(@i), ' ' + CHAR(@i-32))
       SET @i = @i + 1
      END
      SET @string = CHAR(ASCII(LEFT(@string, 1))-32) + RIGHT(@string, LEN(@string)-1)
      RETURN @string
  END
  

  You can easily modify to handle characters after items other than spaces if you wanted to.

  From Josef

• Thomas Owens is right, this should be done in the display layer not in the database. String manipulation and looping are a couple of the things SQL Server is worst at, so they really ought to be avoided in your database. Especially if this will be a popular query.

  From AlexCuse