Want o/p as follows

Question

When I execute a query  select ('NILESH AVHAD')  in sql server 2008 it gives the output as NILESH AVHAD'. But I want oupput as there should be only first letter capital rather than all the letters like I want o/p should be like  Nilesh Avhad  i.e. only first letter capital and others are small. How to write function for this and how to call this function in sql server 2008?

Hemant Srivastava · Answer

Create a Method as: create function ProperCase (@ Text as varchar ( 8000 )) returns varchar ( 8000 ) as begin declare @ Reset bit ; declare @ Ret varchar ( 8000 ); declare @ i int ; declare @ c char ( 1 ); select @ Reset = 1  @ i = 1  @ Ret = '' ; while (@ i len (@ Text )) select @ c = substring (@ Text @ i  1 ) @ Ret = @ Ret + case when @ Reset = 1 then UPPER (@ c ) else LOWER (@ c ) end  @ Reset = case when @ c like '[a-zA-Z]' then 0 else 1 end  @ i = @ i +1 return @ Ret end Then Execute the following command declare @ret varchar(50) EXEC @ret = dbo.ProperCase 'NILESH AVHAD'; print @ret

Nilesh Avhad · Answer

Thanks. It works.