SQL Server UDF: Function to Convert Text String to Title Case (Proper Case)

Tutorialsrack 12/01/2025 SQL SQL Server

When working with textual data in SQL Server, formatting text to Title Case (also known as Proper Case) can be a common requirement. This is especially true in scenarios like creating cleanly formatted reports or standardizing input data. In this article, we’ll guide you through creating a User-Defined Function (UDF) to convert any text string into Title Case, covering everything from beginner-friendly explanations to advanced-level insights.

Introduction

SQL Server provides extensive tools for text manipulation, but it doesn’t have a built-in function for converting strings to Title Case. By creating a User-Defined Function (UDF), we can easily format strings into Title Case, making them easier to read and more professional-looking.

This guide will walk you through building such a function, with detailed examples and real-world use cases to ensure you’re fully equipped to use it effectively.

What is Title Case (Proper Case)?

Title Case refers to a string where the first letter of each word is capitalized, and all other letters are lowercase. For example:

• Input: "hello world"
• Output: "Hello World"

Title Case formatting is commonly used in:

• Names (e.g., "john doe" → "John Doe")
• Titles (e.g., "sql server basics" → "SQL Server Basics")
• Product or brand descriptions.

Why Use a UDF for Title Case Conversion?

Limitations of SQL Server Built-in Functions

• SQL Server provides functions like UPPER() and LOWER() for case manipulation, but they don’t address word-level capitalization.
• There’s no direct built-in function for Title Case conversion.

Benefits of a UDF

• Reusable: A UDF can be called repeatedly across multiple queries.
• Customizable: You can tailor it to ignore specific words (like articles or prepositions).
• Efficiency: By encapsulating logic in a function, you maintain cleaner and more maintainable SQL code.

Creating a UDF to Convert Text to Title Case

Let’s create a scalar-valued UDF in SQL Server. Scalar-valued UDFs return a single value, which makes them perfect for this task.

Step-by-Step Explanation

1. Input: Accept a text string as the input parameter.
2. Split Words: Use a loop or STRING_SPLIT (available in SQL Server 2016 and later) to process each word in the string.
3. Capitalize Words: Capitalize the first letter of each word using string functions like UPPER() and SUBSTRING().
4. Reconstruct the String: Combine the modified words into a single output string.

Code Example

Here’s a simple implementation:

CREATE FUNCTION dbo.fn_TitleCase (@InputString NVARCHAR(MAX))  
RETURNS NVARCHAR(MAX)  
AS  
BEGIN  
    DECLARE @Result NVARCHAR(MAX) = ''  
    DECLARE @Word NVARCHAR(100)  
    DECLARE @Position INT = 0  

    -- Add a space at the end to process the last word
    SET @InputString = LTRIM(RTRIM(@InputString)) + ' '  

    WHILE CHARINDEX(' ', @InputString, @Position + 1) > 0  
    BEGIN  
        SET @Word = SUBSTRING(  
                        @InputString,  
                        @Position + 1,  
                        CHARINDEX(' ', @InputString, @Position + 1) - @Position - 1  
                    )  
        SET @Word = UPPER(LEFT(@Word, 1)) + LOWER(SUBSTRING(@Word, 2, LEN(@Word) - 1))  
        SET @Result = @Result + @Word + ' '  
        SET @Position = CHARINDEX(' ', @InputString, @Position + 1)  
    END  

    RETURN RTRIM(@Result)  
END  
GO  

Detailed Examples with Explanations

Example 1: Basic Input

SELECT dbo.fn_TitleCase('hello world') AS TitleCaseOutput

Output:

Hello World

Explanation:

• The function processes each word individually.
• It capitalizes the first letter and converts the rest to lowercase.

Example 2: Mixed Case Input

SELECT dbo.fn_TitleCase('SQL server is AWESOME') AS TitleCaseOutput

Output:

Sql Server Is Awesome

Explanation:

• Words like "SQL" are not preserved as uppercase since the function is generic. You can customize the UDF to handle such cases.

6. Real-world Use Cases

Use Case 1: Formatting Names in a Database

If you have a table of customer names stored in inconsistent formats:

UPDATE Customers  
SET Name = dbo.fn_TitleCase(Name)  
WHERE IsActive = 1

Use Case 2: Standardizing Product Descriptions

For product listings:

SELECT ProductID, dbo.fn_TitleCase(Description) AS FormattedDescription  
FROM Products  
WHERE Category = 'Electronics'

Performance Considerations

• Efficiency: Scalar UDFs can be slower in large datasets. For better performance, consider rewriting the logic as an inline table-valued function.
• Modern Alternatives: If you’re using SQL Server 2016 or later, STRING_SPLIT simplifies word processing but requires additional logic to maintain word order.

Key Takeaways

1. SQL Server lacks a built-in function for Title Case conversion, but a UDF provides a powerful solution.
2. Title Case is useful in various real-world scenarios, from names to product descriptions.
3. Performance optimization is crucial for large-scale implementations.

Summary

Converting text strings to Title Case in SQL Server is a common task that can be easily handled with a custom UDF. This tutorial covered everything from creating the function to understanding its practical applications. With this knowledge, you can confidently clean and format text data in your SQL Server databases.

Related Posts