uppercase after a dot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

uppercase after a dot

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-09-04 : 14:31:22

hii there i need a function or query that convert my varchar column to something like this

declare @string varchar(max)='ITS RED. MADE IN CHINA. RECEIVED YERSTERDAY'

and after executing the function i need this result (the first letter in uppercase and the letter after a dot in uppercase and the others in lowercase)

@string='Its red. Made in china. Received yesterday'

many thanks for your help

kind regards

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-04 : 14:54:56

Take a look at Madhivanan's blog here: http://beyondrelational.com/modules/2/blogs/70/posts/10901/tsql-initcap-function-convert-a-string-to-proper-case.aspx

He is showing you how to capitalize every word based on each word being preceded by a space. You can sort of extend that as shown below. This depends on one (and only one) space between a period and the letter you want to capitalize.

declare @string varchar(1000), @proper_string varchar(1000)
--set @string='THis is fOR teSTIng'
set @string='ITS RED. MADE IN CHINA. RECEIVED YERSTERDAY'
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources