How to capitalize 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)
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

As a table-valued function:

CREATE FUNCTION dbo.InitCap(@v AS VARCHAR(MAX))
RETURNS TABLE
AS
RETURN 
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 a
GO

Note that string_split requires COMPATIBILITY_LEVEL 130.

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.

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)
--------------------------------------------------
Sales
Management
Production
Development

;WITH StudentList(Name) AS (
      SELECT CONVERT(varchar(50), 'Carl-VAN')
UNION SELECT 'Dean o''brian'
UNION SELECT 'Andrew-le-Smith'
UNION SELECT 'Eddy thompson'
UNION SELECT 'BOBs-your-Uncle'
), Student AS (
    SELECT CONVERT(varchar(50), UPPER(LEFT(Name, 1)) + LOWER(SUBSTRING(Name, 2, LEN(Name)))) Name, 
           pos = PATINDEX('%[-'' ]%', Name)
    FROM StudentList
    UNION ALL
    SELECT CONVERT(varchar(50), LEFT(Name, pos) + UPPER(SUBSTRING(Name, pos + 1, 1)) + SUBSTRING(Name, pos + 2, LEN(Name))) Name, 
           pos = CASE WHEN PATINDEX('%[-'' ]%', RIGHT(Name, LEN(Name) - pos)) = 0 THEN 0 ELSE pos + PATINDEX('%[-'' ]%', RIGHT(Name, LEN(Name) - pos)) END
    FROM Student
    WHERE pos > 0
)
SELECT Name
FROM Student 
WHERE pos = 0
ORDER BY Name 

This will result in:

• Andrew-Le-Smith
• Bobs-Your-Uncle
• Carl-Van
• Dean O'Brian
• Eddy Thompson

Using a recursive CTE set based query should out perform a procedural while loop query. Here I also have made my separate to be 3 different characters [-' ] instead of 1 for a more advanced example. Using PATINDEX as I have done allows me to look for many characters. You could also use CHARINDEX on a single character and this function excepts a third parameter StartFromPosition so I could further simply my 2nd part of the recursion of the pos formula to (assuming a space): pos = CHARINDEX(' ', Name, pos + 1).

It can be as simple as this:

DECLARE @Name VARCHAR(500) = 'Roger';

SELECT @Name AS Name, UPPER(LEFT(@Name, 1)) + SUBSTRING(@Name, 2, LEN(@Name)) AS CapitalizedName;

The suggested function works fine, however, if you do not want to create any function this is how I do it:

select ID,Name
,string_agg(concat(upper(substring(value,1,1)),lower(substring(value,2,len(value)-1))),' ') as ModifiedName 
from Table_Customer 
cross apply String_Split(replace(trim(Name),'  ',' '),' ')
where Name is not null
group by ID,Name;

The above query split the words by space (' ') and create different rows of each having one substring, then convert the first letter of each substring to upper and keep remaining as lower. The final step is to string aggregate based on the key.

I was looking for the best way to capitalize and i recreate simple sql script

how to use SELECT dbo.Capitalyze('this is a test with multiple spaces')

result "This Is A Test With Multiple Spaces"

CREATE FUNCTION Capitalyze(@input varchar(100) )
    returns varchar(100)
as
begin
    
    declare @index int=0
    declare @char as varchar(1)=' '
    declare @prevCharIsSpace as bit=1
    declare @Result as varchar(100)=''

    set @input=UPPER(LEFT(@input,1))+LOWER(SUBSTRING(@input, 2, LEN(@input)))
    set @index=PATINDEX('% _%',@input)
    if @index=0
        set @index=len(@input)
    set @Result=substring(@input,0,@index+1)

    WHILE (@index < len(@input))
    BEGIN
        SET @index = @index + 1
        SET @char=substring(@input,@index,1)
        if (@prevCharIsSpace=1)
        begin
            set @char=UPPER(@char)
            if (@char=' ')
                set @char=''
        end

        if (@char=' ')
            set @prevCharIsSpace=1
        else
            set @prevCharIsSpace=0

        set @Result=@Result+@char
        --print @Result
    END
    --print @Result
    return @Result
end

fname is column name if fname value is akhil then UPPER(left(fname,1)) provide capital First letter(A) and substring function SUBSTRING(fname,2,LEN(fname)) provide(khil) concate both using + then result is (Akhil)

select UPPER(left(fname,1))+SUBSTRING(fname,2,LEN(fname)) as fname
FROM [dbo].[akhil]

On SQL Server 2016+ using JSON which gives guaranteed order of the words:

CREATE FUNCTION [dbo].[InitCap](@Text NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN STUFF((
        SELECT ' ' + UPPER(LEFT(s.value,1)) + LOWER(SUBSTRING(s.value,2,LEN(s.value)))
        FROM OPENJSON('["' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Text,'\','\\'),'"','\"'),CHAR(9),'\t'),CHAR(10),'\n'),' ','","') + '"]') s
        ORDER BY s.[key]
    FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'');
END

GO
CREATE FUNCTION [dbo].[Capitalize](@text NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = '';
    DECLARE @c NVARCHAR(1);
    DECLARE @i INT = 1;
    DECLARE @isPrevSpace BIT = 1;

    WHILE @i <= LEN(@text)
    BEGIN
        SET @c = SUBSTRING(@text, @i, 1);
        SET @result += IIF(@isPrevSpace = 1, UPPER(@c), LOWER(@c));
        SET @isPrevSpace = IIF(@c LIKE '[    -]', 1, 0);
        SET @i += 1;
    END
    RETURN @result;
END
GO

DECLARE @sentence NVARCHAR(100) = N'i-thINK-this    soLUTION-works-LiKe-a charm';
PRINT dbo.Capitalize(@sentence);
-- I-Think-This Solution-Works-Like-A Charm

For English only data.

Super non-efficient from view of performance but efficient from view of productivity. Use it as one-time converter:

SELECT 
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(

UPPER(LEFT(City,1))+LOWER(SUBSTRING(City,2,LEN(City)))

,' a', ' A')
,' b', ' B')
,' c', ' C')
,' d', ' D')
,' e', ' E')
,' f', ' F')
,' g', ' G')
,' h', ' H')
,' i', ' I')
,' j', ' J')
,' k', ' K')
,' l', ' L')
,' m', ' M')
,' n', ' N')
,' o', ' O')
,' p', ' P')
,' q', ' Q')
,' r', ' R')
,' s', ' S')
,' t', ' T')
,' u', ' U')
,' v', ' V')
,' w', ' W')
,' x', ' X')
,' y', ' Y')
,' z', ' Z')


FROM [Dictionaries].[dbo].[Cities]
  WHERE Country = 'US' AND City like '% %'
  ORDER BY City

IF OBJECT_ID ('dbo.fnCapitalizeFirstLetterAndChangeDelimiter') IS NOT NULL
    DROP FUNCTION dbo.fnCapitalizeFirstLetterAndChangeDelimiter
GO

CREATE FUNCTION [dbo].[fnCapitalizeFirstLetterAndChangeDelimiter] (@string NVARCHAR(MAX), @delimiter NCHAR(1), @new_delimeter NCHAR(1))
RETURNS NVARCHAR(MAX)
AS 
BEGIN
    DECLARE @result NVARCHAR(MAX)
    SELECT @result = '';
    IF (LEN(@string) > 0)
        DECLARE @curr INT
        DECLARE @next INT
        BEGIN
            SELECT @curr = 1
            SELECT @next = CHARINDEX(@delimiter, @string)
            WHILE (LEN(@string) > 0)
                BEGIN
                    SELECT @result = 
                        @result + 
                        CASE WHEN LEN(@result) > 0 THEN @new_delimeter ELSE '' END +
                        UPPER(SUBSTRING(@string, @curr, 1)) + 
                        CASE 
                            WHEN @next <> 0 
                            THEN LOWER(SUBSTRING(@string, @curr+1, @next-2))
                            ELSE LOWER(SUBSTRING(@string, @curr+1, LEN(@string)-@curr))
                        END
                    IF (@next > 0)
                        BEGIN
                            SELECT @string = SUBSTRING(@string, @next+1, LEN(@string)-@next)
                            SELECT @next = CHARINDEX(@delimiter, @string)
                        END
                    ELSE
                        SELECT @string = ''
                END
        END
    RETURN @result
END
GO