changing from all upper to mixed 'title' case

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 2000 Forums

Transact-SQL (2000)

changing from all upper to mixed 'title' case

Author

Topic

bdutton
Starting Member

2 Posts

Posted - 2004-07-21 : 11:32:38

How can I take a column in a table and convert it's contents from uppercase to a mixed/title case.

For example:

Change this: HELLO MY NAME IS BOB
to this: Hello My Name Is Bob

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-21 : 11:38:24

This job would be better suited to your presentation layer app.

For a single word, something like upper(left(yourcol,1)) + lower (right(yourcol,(len(yourcol-1)))

You could probably functionalise that and make it deal with spaces and such. Slow.

-------
Moo. :)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-21 : 19:55:44

Yes, as Mr. Mist says, this would be slow. There's no built-in Title Case formatting function. You might Google it because I've seen some home-made versions out there. Also, if your database is not case sensitive, the UPDATE may not behave the way you'd expect.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-21 : 23:26:05

See Corey's posting here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36527[/url] The term for what you are doing is converting to "proper case". Googleing for that should turn up more info. Here is another vesion: [url]http://vyaskn.tripod.com/code/propercase.txt[/url]

Remembered this too:


CREATE FUNCTION dbo.fCapFirst(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS 
BEGIN
DECLARE @position INT
WHILE IsNull(@position,Len(@input)) > 1
SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))), 
@position = charindex(' ',@input,IsNull(@position,1)) + 1
RETURN (@input)
END

It's not as sophisticated as the others but it's about 3 times as fast:

select dbo.fCapFirst(Lower(Column)) From MyTable

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources