Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (385) · Preferences

TSQL InitCap function - Convert a string to Proper Case

Jul 19 2010 6:11AM by Madhivanan

Often people ask how to use InitCap function in SQL Server
Initcap is used to capitalise each first letter of the word and keep rest as non-capital

Pinal Dave has a post at http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text-string-to-title-case-proper-case
Joe Celko gave an idea of nesting the replace function in the comment section

I have simplfied his suggestion and here it is

declare @string varchar(1000), @proper_string varchar(1000)
set @string='THis is fOR teSTIng'
set @proper_string=lower(@string)
set @proper_string=
		replace(replace(replace(replace(replace(replace(replace(
		replace(replace(replace(replace(replace(replace(replace(
		replace(replace(replace(replace(replace(replace(replace(
		replace(replace(replace(replace(replace(
			' '+@proper_string,
		' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
		' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
		' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
		' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
		' y',' Y'),' z',' Z')

select ltrim(@string) as original_string,ltrim(@proper_string) as Init_cap

Tags: t-sql, sql_server, tsql, BRH, initcap, #TSQL, proper_case,

Previous: SQL Server - Having fun with DOT while writing TSQL code
Next: SQL Server - Difference between TSQL Functions CAST() and CONVERT()

Madhivanan

3 · 39% · 12429

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

4 Comments

Something else with the Recursive Replacement, which can be encapsulated in a function.

; WITH RecReplacer AS (Select 1 as idx, CAST(' '+ @ProperString AS VARCHAR(MAX)) As String , CHAR(65) q, CHAR(97) w union all select idx + 1, REPLACE(String, ' ' + CHAR(idx + 65 + 32), ' '+ CHAR(idx + 65)), CHAR(idx + 64), CHAR(idx + 64 + 32) from RecReplacer a Where idx < 27) Select LTRIM(RTRIM(String)) CapString from RecReplacer WHERE idx = 27

commented on Aug 2 2010 6:33AM

Arumugam

370 · 0% · 107

I believe T-SQL Beginner's challenge #22 also has something similar, and more challenging because it needs to be done in a single query!

commented on Dec 27 2010 12:19AM

Nakul Vachhrajani

4 · 33% · 10575

DECLARE @STRING VARCHAR(100) SET @STRING = 'rAJkUMAR iS A GOOD BOY' SELECT UPPER(SUBSTRING(@STRING,1,1))+LOWER(SUBSTRING(@STRING,2,LEN(@STRING)-1))

this will do rite?>?

commented on Jun 16 2011 7:53AM

Rajkumar

278 · 0% · 153

rajbluesea, every first letter of a word should be in capital letter. Your code just capitalise the first letter of first word. Please read the post again

commented on Jun 16 2011 8:54AM

Madhivanan

3 · 39% · 12429

Your Comment

Sign Up or Login to post a comment.

"TSQL InitCap function - Convert a string to Proper Case" rated 5 out of 5 by 2 readers +Madhivanan