Converting to title case - 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 2000 Forums

Transact-SQL (2000)

Converting to title case

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-05-20 : 05:41:43

Hi,
I have a table storing customers names that I want to put into title case, as some are in lower and upper case.

I therefore want to change PAUL ROWLING or paul rowling to Paul Rowling.

How can I do this with an sql statement??

Cheers

Paul

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-20 : 06:45:13

First off, how are you storing the names? This task (and others) is much easier if you store them as separate first and last name fields.

In general, you can do something like

select ltrim (convert (char(1),upper(left(yourcolumn,1))) + convert (varchar(255),lower(right(yourcolumn,len(yourcolumn)-1))))

To convert yourcolumn 'MOO' to 'Moo'.

However, if you want to do this on two column item, you would have to mess about finding spaces and such like, which it may be easier to do beforehand and split the column into two.

-------
Moo.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-05-20 : 07:14:15

Hi,

The forename and surname are in seperate columns so I don't have to mess about stripping them apart.

Cheers

Paul

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-20 : 07:39:00

No need for converts if it is already a varchar() field.

UPPER(LEFT(Field,1)) + LOWER(SUBSTRING(Field,2,1000))

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-20 : 08:02:39

Well I hope you're not intending the victims to see their names mangled in such a simplistic way!

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-20 : 08:23:08

Don't forget to account for names like

O'Donnell
Benton-Smith
De LaRopa

and so on

Sam

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-05-20 : 08:42:29

How will I get round names like these?

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-20 : 09:03:48

quote:

How will I get round names like these?

With difficulty.

-------
Moo.

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2003-05-20 : 09:04:56

The hard way probably......

We had to do something similar and you just can't cater for every eventuality. Especially if your DB contains foreign names such as Dutch. e.g. Edwin van der Sar

Notice that some parts do not have capitalised letters

====
Paul

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-20 : 09:09:40

you mean everyone doesn't have a name like Jeff Smith ?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources