SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 changing from all upper to mixed 'title' case		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bdutton
Starting Member

2 Posts
Posted - 07/21/2004 :  11:32:38  Show Profile  Reply with Quote
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

United Kingdom
1870 Posts
Posted - 07/21/2004 :  11:38:24  Show Profile  Visit mr_mist's Homepage  Reply with Quote
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. :)
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts
Posted - 07/21/2004 :  19:55:44  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts
Posted - 07/21/2004 :  23:26:05  Show Profile  Reply with Quote
See Corey's posting here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36527 The term for what you are doing is converting to "proper case". Googleing for that should turn up more info. Here is another vesion: http://vyaskn.tripod.com/code/propercase.txt

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'
Edited by - kselvia on 07/21/2004 23:34:13
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000