When working with SQL Server, it is often necessary to manipulate the case of characters in a string. The SQL UPPER function and SQL LOWER function are two useful functions that allow you to convert the character case to uppercase and lowercase, respectively.

SQL UPPER Function

The SQL UPPER function is used to convert all characters in an expression to uppercase. It is particularly useful when you want to standardize the case of data in your database. The syntax of the SQL UPPER function is as follows:

SELECT UPPER(expression) FROM [Source Data]

Here are a few examples of how the SQL UPPER function can be used:

SELECT UPPER('sqlshack');

Output: “SQLSHACK”

SELECT UPPER('learn sql server with sqlshack');

Output: “LEARN SQL SERVER WITH SQLSHACK”

The SQL UPPER function can also be used in conjunction with SELECT and UPDATE statements to convert the case of specific columns or variables.

SQL LOWER Function

The SQL LOWER function, on the other hand, is used to convert all characters in an expression to lowercase. It is the opposite of the SQL UPPER function. The syntax of the SQL LOWER function is as follows:

SELECT LOWER(Expression) FROM Source

Here are a few examples of how the SQL LOWER function can be used:

SELECT LOWER('SQLSHACK');

Output: “sqlshack”

SELECT LOWER('LEARN SQL SERVER WITH SQLSHACK');

Output: “learn sql server with sqlshack”

Similar to the SQL UPPER function, the SQL LOWER function can also be used in SELECT and UPDATE statements to convert the case of specific columns or variables.

Convert the First Letter of Each Word in Uppercase

There may be cases where you want to convert the first letter of each word in a string to uppercase, while keeping the rest of the characters in lowercase. Unfortunately, SQL Server does not provide a built-in function for this task. However, you can create a custom function to achieve the desired result.

Here is an example of a custom function that converts the first letter of each word in a string to uppercase:

CREATE OR ALTER FUNCTION [dbo].[ConvertFirstLetterinCapital] (@Text VARCHAR(5000))
RETURNS VARCHAR(5000)
AS
BEGIN
    DECLARE @Index INT;
    DECLARE @FirstChar CHAR(1);
    DECLARE @LastChar CHAR(1);
    DECLARE @String VARCHAR(5000);
    
    SET @String = LOWER(@Text);
    SET @Index = 1;
    
    WHILE @Index <= LEN(@Text)
    BEGIN
        SET @FirstChar = SUBSTRING(@Text, @Index, 1);
        SET @LastChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING(@Text, @Index - 1, 1) END;
        
        IF @LastChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(', '#', '*', '$', '@')
        BEGIN
            IF @FirstChar != '''' OR UPPER(@FirstChar) != 'S'
                SET @String = STUFF(@String, @Index, 1, UPPER(@FirstChar));
        END;
        
        SET @Index = @Index + 1;
    END;
    
    RETURN @String;
END;

You can use this custom function by providing the text as an input value:

SELECT [dbo].[ConvertFirstLetterinCapital]('FRUITS: - APPLE MANGO ORANGE BANANA');

Output: “Fruits: – Apple Mango Orange Banana”

By understanding and utilizing the SQL UPPER function, SQL LOWER function, and creating custom functions, you can effectively manipulate the case of characters in your SQL Server database.

Thank you for reading!