zaro

How to Make the First Letter of Each Word Capital in SQL Server?

Published in SQL String Manipulation 4 mins read

Capitalizing the first letter of each word in SQL Server, often referred to as "Proper Case" or "Title Case," requires a custom approach as SQL Server does not have a built-in function like PROPER() or INITCAP() found in some other database systems. While there isn't a single direct function, it can be achieved using a User-Defined Function (UDF) or a combination of string manipulation functions.

Capitalizing the First Letter of Each Word (Proper Case)

To transform text into proper case, where the first letter of every word is capitalized and the rest of the letters are lowercase, you typically need to create a User-Defined Function (UDF). This function can handle various complexities such as multiple spaces, leading/trailing spaces, and ensure consistent capitalization.

Approach for a User-Defined Function (UDF)

A common strategy for creating a ProperCase UDF involves the following steps:

  • Trim and Lowercase: Begin by trimming any leading or trailing spaces from the input string and converting the entire string to lowercase. This standardizes the text for processing.
  • Capitalize First Character: Capitalize the very first character of the string.
  • Iterate and Capitalize After Spaces: Loop through the rest of the string. Whenever a space is encountered, capitalize the character immediately following that space. This ensures every new word starts with an uppercase letter.

Example of how a ProperCase UDF would be used:

-- Assuming a User-Defined Function named dbo.ProperCase exists
SELECT dbo.ProperCase('this is a test string from sql server') AS ProperCaseString;
-- Expected Output: 'This Is A Test String From Sql Server'

For more robust scenarios, a custom UDF is the most flexible and maintainable solution.

Capitalizing Only the First Letter of a String

If your goal is to capitalize only the very first letter of an entire string while converting the rest to lowercase, SQL Server provides straightforward string manipulation functions. This is useful for single words or phrases where only the initial character needs to be uppercase.

To capitalize the first character of a string, you can use the LEFT() function to extract the initial character and the UPPER() function to convert it to uppercase. The remaining part of the string can be converted to lowercase using LOWER() and extracted using SUBSTRING().

Using UPPER(), LEFT(), SUBSTRING(), and LOWER()

This method combines several string functions to achieve the desired capitalization:

  • UPPER(LEFT(string, 1)): Extracts the first character of the string and converts it to uppercase.
  • LOWER(SUBSTRING(string, 2, LEN(string))): Extracts the remainder of the string (from the second character to the end) and converts it entirely to lowercase.
  • Concatenation (+): These two parts are then joined together.

SQL Example:

SELECT
    InputString,
    UPPER(LEFT(InputString, 1)) + LOWER(SUBSTRING(InputString, 2, LEN(InputString))) AS CapitalizedFirstLetter
FROM (VALUES
    ('hello world in sql'),
    ('SQL SERVER functions'),
    ('another EXAMPLE'),
    ('singleword')
) AS Data(InputString);

Results:

InputString CapitalizedFirstLetter
hello world in sql Hello world in sql
SQL SERVER functions Sql server functions
another EXAMPLE Another example
singleword Singleword

This approach effectively capitalizes the first letter of the entire string while ensuring the rest of the characters are lowercase.

Key Considerations

  • User-Defined Functions (UDFs): While effective for "Proper Case," scalar UDFs (which return a single value) can sometimes impact query performance, especially when applied to a large number of rows. For performance-critical applications, consider alternative methods like Common Language Runtime (CLR) functions or processing in application layer if appropriate.
  • Edge Cases: When implementing custom solutions for "Proper Case," remember to account for edge cases such as leading/trailing spaces, multiple spaces between words, strings containing numbers or special characters, and empty strings.
  • Clarity and Maintainability: For complex string manipulations, encapsulating the logic within a UDF improves code clarity and maintainability, allowing for reusable and centralized logic.