How to capitalize the first letter of each word in a string in SQL Server

Tackling special requirements and considerations

Cap it all, consistently: Large dataset handling

In situations involving substantial volumes of data, it's paramount to devise a set-based solution for optimal performance. Here, we employ recursive Common Table Expressions (CTEs), eliminating the need for performance-degrading cursors and loops.

-- "With great recursion comes great responsibility (of CTEs)" - Spider-Man (SQL Edition)
WITH SplitWords (word, rest_of_string) AS (
    SELECT 
        CAST(LEFT(your_string, CHARINDEX(' ', your_string + ' ') - 1) AS VARCHAR(MAX)) AS word,
        STUFF(your_string, 1, CHARINDEX(' ', your_string + ' '), '') AS rest_of_string
    FROM (SELECT 'your example string' AS your_string) AS string_to_split
    UNION ALL
    SELECT 
        CAST(LEFT(rest_of_string, CHARINDEX(' ', rest_of_string + ' ') - 1) AS VARCHAR(MAX)),
        STUFF(rest_of_string, 1, CHARINDEX(' ', rest_of_string + ' '), '')
    FROM SplitWords
    WHERE rest_of_string > ''
),
CapitalizedWords AS (
    SELECT 
        /* Avengers, Assemble! The great wordcap begins here. */
        UPPER(LEFT(word, 1)) + LOWER(SUBSTRING(word, 2, LEN(word))) AS CapitalizedWord
    FROM SplitWords
)
SELECT STRING_AGG(CapitalizedWord, ' ') FROM CapitalizedWords;


Bent the rules: Constraint handling

SQL Server versions each come with unique quirks. For instance, STRING_SPLIT is only available from SQL Server 2016 onwards. For earlier versions, leverage a different method like a user-defined function or a well-coordinated combo of CHARINDEX and SUBSTRING operations.

Special care for special characters

If your input string houses special characters, your solution should smartly capitalize only the first alphabet following a non-alphabet or space:

-- "Alphabets. They're just like us. Special." - Not really a SQL quote
WITH SplitWords AS (
    SELECT value, CHARINDEX(value, 'your-example-string') AS Pos
    FROM STRING_SPLIT('your-example-string', ' ')
    WHERE value LIKE '[A-Za-z]%'
),
CapitalizedWords AS (
    SELECT 
        STUFF(value, 1, 1, UPPER(LEFT(value, 1))) AS CapitalizedWord, Pos
    FROM SplitWords
)
SELECT STUFF((
    SELECT '' + CapitalizedWord 
    FROM CapitalizedWords 
    ORDER BY Pos 
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '') AS TitleCase;


Implementing a reusable user-defined function

Reuse, Reduce, Recycle: Crafting a user-defined function

For the sake of reusability and isolation, compartmentalize the logic into a user-defined function (UDF):

CREATE FUNCTION dbo.CapitalizeFirstLetter(@InputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    -- "Think once, execute any number of times" - Overzealous UDF maxim 
    RETURN (SELECT STRING_AGG(UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))), ' ') 
            FROM STRING_SPLIT(@InputString, ' '));
END
GO


Apply the UDF as follows:

SELECT dbo.CapitalizeFirstLetter('your example string');
