Sql Server: Making all UPPER case to Proper Case/Title Case

Taking it to the next level—handling exceptions and special cases

It's easy when your casing rules are straightforward, but life often isn't that simple. Let's make our solution robust, malleable, and prepared for all scenarios.

Dealing with acronyms and exceptions

Some acronyms or specialized words should keep the original casing intact—like SQL remaining capitalized. Extending our solution for such cases:

-- A nifty function for special exceptions
CREATE FUNCTION dbo.MaintainAcronyms(@word NVARCHAR(255))
RETURNS NVARCHAR(255)
AS
BEGIN
    RETURN CASE WHEN @word IN ('SQL', 'NASA') THEN @word
                ELSE UPPER(LEFT(@word, 1)) + LOWER(SUBSTRING(@word, 2, LEN(@word)))
           END
END
GO

-- Insert this function into your conversion
SELECT STUFF((
    SELECT ' ' + dbo.MaintainAcronyms(word)
    FROM STRING_SPLIT(REPLACE('Your SQL text here', ' ', '.'), '.')
    FOR XML PATH('')
), 1, 1, '') AS TitleCase;


Embracing non-English characters

Sure, things are easy with the English alphabet. But there's a whole world of text out there with unicode and non-English characters. Here we introduce to our established friend, the COLLATE clause. Accented or international characters? No problem anymore.

Efficient spacing and punctuation: cutting out the fluff

Excessive white space or unwanted punctuation can really mess things up. Here we pre-process the string, replacing multiple spaces with a single space: a quick, practical, pattern-matching approach. Pairing a WHILE loop with REPLACE smooths everything out before title case conversion.

Visualising the text conversion process

Converting UPPER case text to Proper/Title case is like bookshelf organisation:

Before: [SQL, SERVER, DATA, ANALYSIS]

Think of loud, shouting book titles—it's a headache, isn't it?

After: [Sql, Server, Data, Analysis]

📚 That's better. We've reordered our bookshelf. The titles are clear and understandable in Proper Case, making it a breeze to locate each one. 📚

Converting to Proper Case is like going from SCREAMING TITLES to Readable Titles

Creating User-Defined Functions (UDFs) for robust title case conversion

Building UDFs, one brick at a time

Expanding on our MaintainAcronyms function idea, we can step it up with a more dynamic UDF—the ProperCase, looping through each word, checking for exceptions, and ensuring correct casing. Each word gets the royal treatment.

Handling nulls and preserving data integrity

By checking for NULL parameters upfront, our UDFs are now null-safe. We've also used SQL functions like ISNULL and COALESCE in our script to ensure we're controlling results when working with NULL values. That's called being prepared.

Avoiding performance pitfalls

With a monstrous dataset on your hands, efficiency is key when it comes to loops and string manipulations. Use set-based operations whenever possible and remember that recursive CTE or cursor approaches may impact performance.