How to Capitalize only the first letter of each word of each sentence in SQL Server?

2.1K Asked by AnnaBall in SQL Server , Asked on Sep 7, 2021

I want to capitalize only the first letter of each word of each sentence in a SQL column. For example, if the sentence is: 'I like movies' then I need the output: 'I Like Movies' Query:declare @a varchar(15) set @a = 'qWeRtY kEyBoArD' select @a as [Normal text], upper(@a) as [Uppercase text], lower(@a) as [Lowercase text], upper(left(@a,1)) + lower(substring(@a,2,len(@a))) as [Capitalize first letter only] Here I did upper, lower, and capitalize first letter only in my column (here I put just a random word). Here are my results:

enter image description here

Is there any possibilities to do that?

Any possibilities to get results without using a user defined function? I need the output Qwerty Keyboard

Answered by Ankesh Kumar

To solve sql server uppercase you can refer the below mentioned code:

  declare @a varchar(30); set @a = 'qWeRtY kEyBoArD TEST&lt;>&''"X'; select stuff(( select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, '')) from (select cast(replace((select @a as '*' for xml path('')), ' ', '') as xml).query('.')) as T1(X) cross apply T1.X.nodes('text()') as T2(X) cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V) for xml path(''), type ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

This first converts the string to XML by replacing all spaces with the empty tag . Then it shreds the XML to get one word per row using nodes(). To get the rows back to one value it uses the for xml path trick. Hope this helps!

Your Answer

Answer (1)

Ranjana

To capitalize only the first letter of each word in a sentence in SQL Server, you can create a user-defined function to handle the transformation. SQL Server does not have a built-in function for this specific task, so you'll need to write a function to iterate through each word and capitalize the first letter.

Here is an example of how you can create such a function:

  Step-by-Step Guide

Create the Function:

This function will take a string as input, iterate through each word, capitalize the first letter, and then concatenate the words back together.

  CREATE FUNCTION dbo.CapitalizeFirstLetterOfEachWord (@inputString NVARCHAR(MAX))RETURNS NVARCHAR(MAX)ASBEGIN    DECLARE @outputString NVARCHAR(MAX) = '';    DECLARE @currentWord NVARCHAR(100);    DECLARE @position INT = 1;    DECLARE @length INT;    DECLARE @spacePosition INT;    -- Remove leading and trailing spaces    SET @inputString = LTRIM(RTRIM(@inputString));    -- Iterate over each word in the input string    WHILE LEN(@inputString) > 0    BEGIN        -- Find the position of the first space in the remaining string        SET @spacePosition = CHARINDEX(' ', @inputString, @position);        -- If there is no space, the current word is the remaining string        IF @spacePosition = 0        BEGIN            SET @currentWord = @inputString;            SET @inputString = '';        END        ELSE        BEGIN            -- Extract the current word            SET @currentWord = SUBSTRING(@inputString, 1, @spacePosition - 1);            -- Remove the current word from the input string            SET @inputString = LTRIM(RTRIM(SUBSTRING(@inputString, @spacePosition + 1, LEN(@inputString) - @spacePosition)));        END        -- Capitalize the first letter of the current word and concatenate it to the output string        SET @outputString = @outputString + UPPER(LEFT(@currentWord, 1)) + LOWER(SUBSTRING(@currentWord, 2, LEN(@currentWord))) + ' ';    END    -- Remove trailing space from the output string    RETURN RTRIM(@outputString);ENDTest the Function:

Now that you have created the function, you can test it with various input strings to ensure it works correctly.

  -- Test the functionSELECT dbo.CapitalizeFirstLetterOfEachWord('hello world this is a test');-- Expected output: 'Hello World This Is A Test'SELECT dbo.CapitalizeFirstLetterOfEachWord('sql server functions are useful');-- Expected output: 'Sql Server Functions Are Useful'

Usage in Queries:

You can use this function in your SQL queries to transform data as needed.

  -- Example usage in a querySELECT dbo.CapitalizeFirstLetterOfEachWord(columnName) AS CapitalizedColumnFROM yourTable;

Explanation:

Initial Setup: The function initializes variables to hold the input and output strings, the current word, and positions for iterating through the string.

Whitespace Removal: It removes leading and trailing spaces from the input string.

Iteration and Extraction: The function iterates through each word by finding spaces and extracting words.

Capitalization: It capitalizes the first letter of each word and concatenates the word to the output string.

Return Result: Finally, the function returns the transformed string with each word capitalized.

This function will capitalize the first letter of each word in a sentence, and it can be used in SQL queries to format text data as required.

2 Months