how to handle upper and lower case

I want to handle upper and lower case in sql statement on column name "EMPNAME"

Upper(EmpNAME)


EMPNAME=IQBAL MEHRAM

Need to display like this

EMPNAME=Iqbal Mehram
MehramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Below is my T-SQL function for proper case, which will capitalize every first letter in a word.  Keep in mind that if there are exeptions to that, such as 'Claude van der Bork', that it's not going to be able to tell which to capitalize and which not to capitalize.


CREATE FUNCTION fn_proper_case(@str as varchar(8000))returns varchar(8000) as
begin   

/*
Returns the proper case (First letter capitalized, the rest smalls) of a string. 

Version History
03-02-12   jhorn   Original.  http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspx

-- EXAMPLE OF HOW TO CALL
select dbo.ProperCase('this,my friends, is a test.wHat DO you think?i like shaquile o''neal')
This,My Friends, Is A Test.What Do You Think?I Like Shaquile O'Neal

-- TESTING ONLY
Declare @str varchar(8000) = 'IQBAL MEHRAM'
*/

	declare @Reset bit   
	declare @Ret varchar(8000)   
	declare @i int  
	declare @c char(1)

	select @Reset = 1, @i=1, @Ret = ''

	while (@i <= len(@str))   
		select @c= substring(@str,@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

Open in new window

0
Kent DyerIT Security Analyst SeniorCommented:
I think this maybe what you are looking for: "Init Cap"..  Initial character is capitalized..

http://www.sql-server-helper.com/functions/initcap.aspx

HTH,

Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split, but for future reference EE frowns on link-only comments being accepted as an answer, especially those to competing sites, that may become a bad link over time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.