make first letter capital of the word

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.

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;

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

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

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;