make first letter capital of the word

Question

Hi Expert,

how to make first letter capital of the word

create table main13( First_lastname Nvarchar (100))

insert main13 values('18 - EW MEMBER; OLD MEEMBER'),
('18 - VOYGER'),
('1 - ONE DAY')

EXPECTED Output

Answer 1

I think you need a function to archive it. Here is the function which returns the string with first letter capital of each word:

CREATE FUNCTION [dbo].[ufn_Get_String_with_First_Letter_Capital_of_Each_Word] (  
	@inputString varchar(100),  
	@delimiter char(1)  
)  
RETURNS VARCHAR(100)  
AS    
BEGIN   
	DECLARE @outputString varchar(100) = '';  
	DECLARE @word varchar(100) = '';  
	DECLARE @pos int = 0;  
	DECLARE @len int = 0;  
  
	SET @inputString = CASE WHEN RIGHT(@inputString, 1) <> @delimiter THEN @inputString + @delimiter ELSE @inputString END;  
	WHILE CHARINDEX(@delimiter, @inputString, @pos + 1) > 0  
	BEGIN  
		SET @len = CHARINDEX(@delimiter, @inputString, @pos + 1) - @pos;  
		SET @word = SUBSTRING(@inputString, @pos, @len);  
		SET @word = UPPER(LEFT(@word, 1)) + LOWER(SUBSTRING(@word, 2, LEN(@word) - 1))  
  
		IF @pos = 0  
		BEGIN  
			SET @outputString = @word;  
		END  
		ELSE  
		BEGIN  
			SET @outputString = @outputString + ' ' + @word;  
		END  
  
		SET @pos = CHARINDEX(@delimiter, @inputString, @pos + @len) + 1  
	END  
	  
	RETURN @outputString;  
END  
GO  

And then use the following query to the result:

SELECT [dbo].[ufn_Get_String_with_First_Letter_Capital_of_Each_Word](First_lastname, ' ')  
FROM main13;  

Answer 2

John Ross answer is what I was going to suggest as well, but read the comments please as for the names it may be a very tricky problem

https://stackoverflow.com/questions/230138/sql-server-make-all-upper-case-to-proper-case-title-case

Answer 3

what a solution ..is there any easiest way to do this just want to have capital word after space

Answer 4

Hi ShambhuRai

This solution works for me:

declare @foo table
(
 id integer identity(1,1),
 first_lastname varchar(max)
);

insert into @foo values
('18 - EW MEMBER; OLD MEEMBER'),
('18 - VOYGER'),
('1 - ONE DAY');

with cte as
(
select
 f.id,
 new_value = upper(left(s.value, 1)) + SUBSTRING(s.value, 2, len(s.value)),
 word_id   = row_number() over (partition by f.id order by (select null))
from
 @foo f
 cross apply string_split(lower(f.first_lastname), ' ') s
)
select
 c.id,
 First_lastname = string_agg(c.new_value, ' ') within group (order by c.word_id)
from
 cte c
group by
 c.id;

Answer 5

Hi，@Shambhu Rai

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

CREATE FUNCTION Capital_Word(@Text NVARCHAR(200))  
RETURNS NVARCHAR(200)  
AS  
BEGIN  
    RETURN STUFF((  
        SELECT ' ' + UPPER(LEFT(s.value,1)) + LOWER(SUBSTRING(s.value,2,LEN(s.value)))  
        FROM   
 OPENJSON('["' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Text,'\','\\'),'"','\"'),CHAR(9),'\t'),CHAR(10),'\n'),' ','","') + '"]') s  
        ORDER BY s.[key]  
    FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'');  
END  
 
 SELECT First_lastname,dbo.Capital_Word(First_lastname)as Updated_First_lastname  
 FROM #main13;  

Here is the result:

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.