Capitalize First Letter in SQL

Methods:

1. Using the STUFF function:

   • This method involves iterating through each character in the string, identifying the first letter of each word, and capitalizing it using UPPER.
   • The STUFF function is then used to replace the original letter with its capitalized version.

   DECLARE @string VARCHAR(100) = 'this is a sample string';
   
   SELECT STUFF(@string, 1, 1, UPPER(LEFT(@string, 1))) +
          STUFF(@string, CHARINDEX(' ', @string) + 2, 1, UPPER(SUBSTRING(@string, CHARINDEX(' ', @string) + 2, 1))) AS CapitalizedString;
   

2. Using a custom function:

   • You can create a custom function that encapsulates the logic of capitalizing the first letter of each word.
   • This function can then be reused in your SQL queries.

   CREATE FUNCTION CapitalizeFirstLetter(@string VARCHAR(MAX))
   RETURNS VARCHAR(MAX)
   AS
   BEGIN
       DECLARE @result VARCHAR(MAX) = '';
       DECLARE @i INT = 1;
   
       WHILE @i <= LEN(@string)
       BEGIN
           IF @i = 1 OR SUBSTRING(@string, @i - 1, 1) = ' '
           BEGIN
               @result = @result + UPPER(SUBSTRING(@string, @i, 1));
               @i = @i + 1;
           END
           ELSE
           BEGIN
               @result = @result + LOWER(SUBSTRING(@string, @i, 1));
               @i = @i + 1;
           END
       END
   
       RETURN @result;
   END
   
   SELECT CapitalizeFirstLetter('this is a sample string') AS CapitalizedString;
   

3. Using a built-in function (if available):

Explanation:

• The CHARINDEX function finds the position of a substring within a string.
• The custom function iterates through the string, capitalizing the first letter of each word.
• The LEFT and SUBSTRING functions extract specific parts of a string.
• The UPPER function converts a string to uppercase.
• The STUFF function replaces a specified substring within a string with another string.

Method 1: Using the STUFF Function

DECLARE @string VARCHAR(100) = 'this is a sample string';

SELECT STUFF(@string, 1, 1, UPPER(LEFT(@string, 1))) +
       STUFF(@string, CHARINDEX(' ', @string) + 2, 1, UPPER(SUBSTRING(@string, CHARINDEX(' ', @string) + 2, 1))) AS CapitalizedString;

Breakdown:

• AS CapitalizedString: Assigns the result of the concatenation to the alias CapitalizedString.
• STUFF(@string, CHARINDEX(' ', @string) + 2, 1, UPPER(SUBSTRING(@string, CHARINDEX(' ', @string) + 2, 1))):
  • Finds the index of the first space in @string using CHARINDEX(' ', @string).
  • Replaces the character after the space (index CHARINDEX(' ', @string) + 2) with the uppercase version of the character.
  • UPPER(SUBSTRING(@string, CHARINDEX(' ', @string) + 2, 1)) capitalizes the character after the space.
• STUFF(@string, 1, 1, UPPER(LEFT(@string, 1))):
  • Replaces the first character of @string (index 1) with the uppercase version of the first character.
  • UPPER(LEFT(@string, 1)) capitalizes the first character of @string.
• DECLARE @string VARCHAR(100) = 'this is a sample string';: Declares a variable named @string and assigns the string "this is a sample string" to it.

CREATE FUNCTION CapitalizeFirstLetter(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @result VARCHAR(MAX) = '';
    DECLARE @i INT = 1;

    WHILE @i <= LEN(@string)
    BEGIN
        IF @i = 1 OR SUBSTRING(@string, @i - 1, 1) = ' '
        BEGIN
            @result = @result + UPPER(SUBSTRING(@string, @i, 1));
            @i = @i + 1;
        END
        ELSE
        BEGIN
            @result = @result + LOWER(SUBSTRING(@string, @i, 1));
            @i = @i + 1;
        END
    END

    RETURN @result;
END

SELECT CapitalizeFirstLetter('this is a sample string') AS CapitalizedString;

• SELECT CapitalizeFirstLetter('this is a sample string') AS CapitalizedString;: Calls the CapitalizeFirstLetter function with the input string and assigns the result to the alias CapitalizedString.
• RETURN @result;: Returns the capitalized string stored in @result.
• ELSE: If the current character is not the first and the previous character is not a space, appends the lowercase version of the current character to @result.
• IF @i = 1 OR SUBSTRING(@string, @i - 1, 1) = ' ': Checks if the current character is the first character or if the previous character is a space.
  • If true, capitalizes the current character and appends it to @result.
• WHILE @i <= LEN(@string): Iterates through each character in @string.
• DECLARE @i INT = 1;: Declares a variable named @i to iterate through the characters of the input string.
• DECLARE @result VARCHAR(MAX) = '';: Declares a variable named @result to store the capitalized string.
• CREATE FUNCTION CapitalizeFirstLetter(@string VARCHAR(MAX)) RETURNS VARCHAR(MAX): Defines a function named CapitalizeFirstLetter that takes a VARCHAR(MAX) input and returns a VARCHAR(MAX) result.

Alternative Methods for Capitalizing the First Letter of Each Word in SQL Server

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

Using a Common Table Expression (CTE) and Recursive CTE

This method provides a more structured and readable approach, especially for longer strings or more complex scenarios.

WITH cte AS (
    SELECT
        original_string = @string,
        capitalized_string = UPPER(LEFT(@string, 1)) + SUBSTRING(@string, 2, CHARINDEX(' ', @string) - 1),
        start_index = CHARINDEX(' ', @string) + 1
    WHERE
        CHARINDEX(' ', @string) > 0
    UNION ALL
    SELECT
        original_string,
        capitalized_string + ' ' + UPPER(LEFT(SUBSTRING(original_string, start_index, LEN(original_string)), 1)) + SUBSTRING(SUBSTRING(original_string, start_index, LEN(original_string)), 2, CHARINDEX(' ', SUBSTRING(original_string, start_index, LEN(original_string))) - 1),
        CHARINDEX(' ', SUBSTRING(original_string, start_index, LEN(original_string))) + start_index
    FROM
        cte
    WHERE
        CHARINDEX(' ', SUBSTRING(original_string, start_index, LEN(original_string))) > 0
)
SELECT
    capitalized_string
FROM
    cte
WHERE
    start_index = 0;

Using a WHILE loop and CHARINDEX

This approach is more iterative and can be more efficient for very long strings.

DECLARE @string VARCHAR(MAX) = 'this is a sample string';
DECLARE @startIndex INT = 1;
DECLARE @endIndex INT;
DECLARE @capitalizedString VARCHAR(MAX) = '';

WHILE @startIndex < LEN(@string) + 1
BEGIN
    SET @endIndex = CHARINDEX(' ', @string, @startIndex);
    IF @endIndex = 0
        SET @endIndex = LEN(@string) + 1;

    SET @capitalizedString = @capitalizedString + UPPER(LEFT(SUBSTRING(@string, @startIndex, @endIndex - @startIndex), 1)) + LOWER(SUBSTRING(@string, @startIndex + 1, @endIndex - @startIndex - 1));
    SET @startIndex = @endIndex + 1;
END

SELECT @capitalizedString;

Using a scalar-valued function with PATINDEX

This method leverages PATINDEX to find the next word boundary and can be more performant in certain scenarios.

CREATE FUNCTION CapitalizeFirstLetter(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @result VARCHAR(MAX) = '';
    DECLARE @startIndex INT = 1;

    WHILE @startIndex <= LEN(@string)
    BEGIN
        DECLARE @endIndex INT = PATINDEX('% %', @string + ' ') - 1;
        IF @endIndex = 0
            SET @endIndex = LEN(@string);

        SET @result = @result + UPPER(LEFT(SUBSTRING(@string, @startIndex, @endIndex - @startIndex + 1), 1)) + LOWER(SUBSTRING(@string, @startIndex + 1, @endIndex - @startIndex));
        SET @startIndex = @endIndex + 2;
    END

    RETURN @result;
END