Convert Uppercase to Proper/Title Case in SQL Server

Proper Case: Proper case capitalizes the first letter of each word while converting the rest to lowercase.

Title Case: Title case is similar to proper case but also capitalizes specific words (e.g., prepositions, articles) that are typically lowercase in sentences.

SQL Code:

DECLARE @Text VARCHAR(100) = 'THIS IS A SAMPLE TEXT';

-- Proper Case
SELECT UPPER(LEFT(@Text, 1)) + LOWER(RIGHT(@Text, LEN(@Text) - 1)) AS ProperCase;

-- Title Case (Example for common words)
SELECT UPPER(LEFT(@Text, 1)) + LOWER(RIGHT(@Text, LEN(@Text) - 1))
    REPLACE(' AND ', ' and ')
    REPLACE(' OR ', ' or ')
    REPLACE(' THE ', ' the ')
    REPLACE(' A ', ' a ')
    REPLACE(' AN ', ' an ') AS TitleCase;

Explanation:

1. Declare a variable: The DECLARE statement creates a variable named @Text to hold the uppercase text.
2. Convert to uppercase: The UPPER function converts all characters in the variable to uppercase.
3. Proper case:
   • LEFT(@Text, 1) extracts the first character of the text.
   • UPPER(LEFT(@Text, 1)) converts the first character to uppercase.
   • RIGHT(@Text, LEN(@Text) - 1) extracts all characters from the second position to the end.
   • LOWER(RIGHT(@Text, LEN(@Text) - 1)) converts the remaining characters to lowercase.
   • Combining these parts creates the proper case format.
4. Title case:
   • The same logic as proper case is used to capitalize the first letter of each word.
   • Additional REPLACE functions are included to handle common words that are typically lowercase in title case (e.g., "and", "or", "the", "a", "an").

Key points:

• The REPLACE function is optional for handling title case rules.
• The LOWER function converts text to lowercase.
• The LEFT and RIGHT functions are used to extract specific parts of the text.
• The UPPER function is essential for converting text to uppercase.

Proper Case:

DECLARE @Text VARCHAR(100) = 'THIS IS A SAMPLE TEXT';

SELECT UPPER(LEFT(@Text, 1)) + LOWER(RIGHT(@Text, LEN(@Text) - 1)) AS ProperCase;

• Explanation:
  • Convert first letter to uppercase: UPPER(LEFT(@Text, 1)) extracts the first character of the text and converts it to uppercase using the UPPER function.
  • Convert remaining letters to lowercase: LOWER(RIGHT(@Text, LEN(@Text) - 1)) extracts all characters from the second position to the end and converts them to lowercase using the LOWER function.
  • Combine the parts: The + operator concatenates the uppercase first letter with the lowercase remaining letters to form the proper case format.

Title Case:

DECLARE @Text VARCHAR(100) = 'THIS IS A SAMPLE TEXT';

SELECT UPPER(LEFT(@Text, 1)) + LOWER(RIGHT(@Text, LEN(@Text) - 1))
    REPLACE(' AND ', ' and ')
    REPLACE(' OR ', ' or ')
    REPLACE(' THE ', ' the ')
    REPLACE(' A ', ' a ')
    REPLACE(' AN ', ' an ') AS TitleCase;

• Explanation:
  • Similar to proper case: The initial steps are identical to proper case, capitalizing the first letter of each word and converting the rest to lowercase.
  • Handle exceptions: The REPLACE functions are used to address specific words (prepositions, articles) that are typically lowercase in title case. This ensures that the final result adheres to the desired formatting conventions.

Additional Considerations:

• Unicode support: The UPPER and LOWER functions support Unicode characters, ensuring correct case conversion for a wide range of languages.
• Performance: For large datasets, consider using a user-defined function or other optimization techniques to improve performance.
• Customizable exceptions: You can modify the REPLACE functions to include additional exceptions or modify the behavior to suit your specific needs.

Alternative Methods for Converting Uppercase to Proper or Title Case in SQL Server

While the methods provided in the previous responses are effective, here are some alternative approaches you can consider:

Using a User-Defined Function (UDF)

A UDF can be created to encapsulate the logic for converting uppercase to proper or title case. This can improve code readability and maintainability, especially if the conversion is needed in multiple places.

CREATE FUNCTION dbo.ProperCase (@Text VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Result VARCHAR(MAX) = UPPER(LEFT(@Text, 1)) + LOWER(RIGHT(@Text, LEN(@Text) - 1));

    -- Add title case exceptions here

    RETURN @Result;
END

Employing a CLR (Common Language Runtime) Assembly

For complex conversion rules or performance optimization, a CLR assembly can be created in a .NET language (like C#) and deployed to SQL Server. This allows you to leverage the power of .NET libraries and potentially improve performance.

Utilizing a Full-Text Index

If you're dealing with large datasets and need efficient searching, consider using a full-text index. While not directly for conversion, full-text indexes can be used to search for specific words or phrases in proper or title case formats.

Leveraging Third-Party Tools or Libraries

There might be third-party tools or libraries available that provide specialized functions for text manipulation, including case conversion. These can offer additional features or performance benefits.

Using a Stored Procedure

If the conversion is part of a larger process, creating a stored procedure can help organize the code and make it easier to manage.

Choosing the Right Method The best method depends on your specific requirements, such as:

• Maintainability: Choose a method that is easy to understand and maintain for your team.
• Integration with other systems: If you're using a third-party tool or library, ensure it integrates well with your existing SQL Server environment.
• Performance needs: If performance is critical, consider using a CLR assembly or optimizing your UDF.
• Complexity of conversion rules: For simple rules, a UDF might suffice, while complex rules might benefit from a CLR assembly.