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

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

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) RETURNS VARCHAR(4000)ASBEGINDECLARE @Index          INTDECLARE @Char           CHAR(1)DECLARE @PrevChar       CHAR(1)DECLARE @OutputString   VARCHAR(255)SET @OutputString = LOWER(@InputString)SET @Index = 1WHILE @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 + 1ENDRETURN @OutputStringENDGO

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

If you are looking for the answer to the same question in Oracle/PLSQL then you may use the function INITCAP. Below is an example for the attribute dname from a table department which has the values ('sales', 'management', 'production', 'development').

SQL> select INITCAP(dname) from department;INITCAP(DNAME)--------------------------------------------------SalesManagementProductionDevelopment

As a table-valued function:

CREATE FUNCTION dbo.InitCap(@v AS VARCHAR(MAX))RETURNS TABLEASRETURN WITH a AS (    SELECT (        SELECT UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))) AS 'data()'        FROM string_split(@v, ' ')        ORDER BY CHARINDEX(value,@v)        FOR XML PATH (''), TYPE) ret)SELECT CAST(a.ret AS varchar(MAX)) ret from aGO

Note that string_split requires COMPATIBILITY_LEVEL 130.