SQL Server Proper Case: 4 Practical Methods

Unlike Oracle (INITCAP) or Microsoft Excel (PROPER), Microsoft SQL Server has never included a native function to convert text into title case. When cleaning legacy imports, normalizing user input, or building formatted reports, implementing a reliable sql server proper case solution is a common hurdle database administrators and developers face.

While SQL Server provides UPPER() and LOWER(), turning "john doe" or "JANE DOE" into "John Doe" requires custom logic. Below, we explore four different ways to accomplish proper casing in SQL Server, ranging from simple single-word formulas to scalable set-based functions.

Method 1: The Single-Word Expression (Quick & Lightweight)

If your dataset contains single words without spaces—such as usernames, first names stored in an isolated column, or category codes—you do not need complex loops or functions. A direct combination of UPPER(), LOWER(), and string slicing functions will get the job done efficiently.

SELECT 
    UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, LEN(FirstName))) AS ProperFirstName
FROM dbo.Users;

This approach runs inline, supports query parallelism, and avoids user-defined function (UDF) overhead. However, it fails on multi-word strings like "john doe", converting it to "John doe" rather than capitalizing each individual word.

Method 2: Modern SQL Server Proper Case Using STRING_SPLIT and STRING_AGG

Starting with SQL Server 2022, STRING_SPLIT supports an optional third argument: enable_ordinal. This parameter guarantees the preservation of word order, allowing you to build an inline, set-based title casing function without traditional looping.

CREATE OR ALTER FUNCTION dbo.fn_ProperCase_Modern (@Input NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
    WITH Words AS (
        SELECT 
            ordinal,
            UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))) AS FormattedWord
        FROM STRING_SPLIT(@Input, ' ', 1) -- '1' enables ordinal in SQL Server 2022+
        WHERE value <> ''
    )
    SELECT STRING_AGG(FormattedWord, ' ') WITHIN GROUP (ORDER BY ordinal) AS ProperValue
    FROM Words
);

You can execute this inline table-valued function (iTVF) efficiently across a large table using CROSS APPLY:

SELECT 
    u.FullName,
    p.ProperValue
FROM dbo.Users u
CROSS APPLY dbo.fn_ProperCase_Modern(u.FullName) p;

Note on SQL Server 2017 & 2019: While STRING_AGG exists in SQL Server 2017+, STRING_SPLIT does not support the enable_ordinal flag until SQL Server 2022. Without ordinal support, word order is not guaranteed. If you are on an older version, use Method 3 or Method 4.

Method 3: Classic Character-by-Character Scalar Function

For SQL Server 2019 and older, the traditional method uses a scalar User-Defined Function (UDF). This function traverses the string, identifies whitespace and punctuation delimiters, and capitalizes characters immediately following a separator.

CREATE OR ALTER FUNCTION dbo.fn_ProperCase_Classic (@Input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    IF @Input IS NULL
        RETURN NULL;

    DECLARE @Output NVARCHAR(MAX) = LOWER(@Input);
    DECLARE @Index INT = 1;
    DECLARE @Len INT = LEN(@Input);
    DECLARE @NextCharUpper BIT = 1;
    DECLARE @CurrentChar NCHAR(1);

    WHILE @Index <= @Len
    BEGIN
        SET @CurrentChar = SUBSTRING(@Output, @Index, 1);

        IF @CurrentChar IN (' ', '-', '.', ''', '/', '(', '[')
        BEGIN
            SET @NextCharUpper = 1;
        END
        ELSE IF @NextCharUpper = 1
        BEGIN
            SET @Output = STUFF(@Output, @Index, 1, UPPER(@CurrentChar));
            SET @NextCharUpper = 0;
        END

        SET @Index = @Index + 1;
    END

    RETURN @Output;
END;

Performance Caveat

Scalar UDFs can hurt query performance when used on large datasets because they traditionally force row-by-row execution (RBAR) and prevent query parallelism. While SQL Server 2019 introduced Scalar UDF Inlining, functions containing WHILE loops cannot be inlined. Reserve this function for small batch updates or ad-hoc data cleansing routines.

Method 4: High-Performance Processing with SQL CLR

When you need to format millions of strings across complex rules (such as maintaining unicode properties or respecting specific cultural locales), a SQL CLR (Common Language Runtime) function is significantly faster than T-SQL looping.

The .NET framework includes a highly optimized TextInfo.ToTitleCase method inside the System.Globalization namespace. A C# CLR method looks like this:

using System.Data.SqlTypes;
using System.Globalization;
using Microsoft.SqlServer.Server;

public class StringFormatters
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString ToProperCase(SqlString input)
    {
        if (input.IsNull)
            return SqlString.Null;

        TextInfo textInfo = CultureInfo.CurrentCulture.TextInfo;
        return new SqlString(textInfo.ToTitleCase(input.Value.ToLower()));
    }
}

Because .NET executes compiled CIL bytecode, a CLR proper-casing function often outperforms an equivalent T-SQL scalar loop by a factor of 10 to 50 on large queries.

Handling Real-World Data Edge Cases

No mathematical casing algorithm handles every nuance of human names and addresses. Before deploying your formatting routine to production, consider these frequent pitfalls:

• Hyphenated & Prefixed Names: Surnames such as Smith-Jones, O'Connor, or MacDonald require rules beyond simple space delimitation. Method 3 handles hyphens and apostrophes, but capitalized inner letters (like the 'D' in MacDonald) require custom lookup dictionaries.
• Corporate Abbreviations: An automated proper-case function will turn "IBM LLC" into "Ibm Llc" or "USA" into "Usa". If corporate names or abbreviations exist in your data, maintain an exclusion list for known acronyms.
• Roman Numerals: Suffixes like Richard III or Henry VII become Iii and Vii. These generally need post-processing replacement statements.

Which Approach Should You Use?

1. SQL Server 2022 and newer: Use Method 2 (STRING_SPLIT with ordinal + STRING_AGG). It is set-based, pure T-SQL, and easy to maintain.
2. SQL Server 2019 and older (Small Datasets): Use Method 3. It is simple to install and requires no server configuration changes.
3. Massive Data Warehousing / Large Batches: Use Method 4 (SQL CLR) or perform the transformation upstream during ETL/ELT (e.g., in SSIS, Azure Data Factory, or Python).
4. UI Layer Formatting: In many modern architectures, the best place to handle casing is not the database engine at all, but the presentation tier (C#, JavaScript, or Power BI), saving valuable database CPU cycles for relational queries.