ProperCase a Name - 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 2005 Forums

Transact-SQL (2005)

ProperCase a Name

Author

Topic

gj0519
Starting Member

13 Posts

Posted - 2008-07-08 : 12:27:49

I am working on creating a function that will take a name that has a comma for the delimiter and propercase the last and first name.
I can get the last name to work but I am having problems getting the first name set to propercase.

Original name: SHORT, GALE
After I run my function: Short, gale

Create Function dbo.fn_ProperCaseName(@FullName VarChar(30))
Returns VarChar(30)
As
Begin
 Declare @CommaIndex TinyInt, @ReturnName VarChar(30)

Set @CommaIndex = CharIndex(',',@FullName)

If @CommaIndex > 0
	Set @ReturnName = Upper(Left(@FullName,1))
	+ Lower(Substring(@FullName, 2, @CommaIndex -1))
	+ Upper(Substring(@FullName, @CommaIndex + 1, 1))
	+ Lower(Substring(@FullName, @CommaIndex +2, Len(@FullName)))
Else 
	Set @ReturnName = Upper(Left(@FullName,1))
	+ Lower(Substring(@FullName, 2, Len(@FullName)))
Return @ReturnName
End

Thanks,
GJ

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-08 : 12:52:35

Here's a quick fix, but it won't handle any name that is not
formatted as LastName(one word),(space) FirstName.
You need to take the space between the names into account.

Declare @CommaIndex TinyInt, @ReturnName VarChar(30)
DECLARE @fullname varchar(50)

SET @fullname = 'smith, john'

Set @CommaIndex = CharIndex(',',@FullName)

If @CommaIndex > 0
Set @ReturnName = Upper(Left(@FullName,1))
+ Lower(Substring(@FullName, 2, @CommaIndex))
+ Upper(Substring(@FullName, @CommaIndex + 2, 1))
+ Lower(Substring(@FullName, @CommaIndex +3, Len(@FullName)))
Else
Set @ReturnName = Upper(Left(@FullName,1))
+ Lower(Substring(@FullName, 2, Len(@FullName)))
select @ReturnName
Jim

gj0519
Starting Member

13 Posts

Posted - 2008-07-08 : 13:06:28

Thanks for the reply Jim,

I just figured out where I needed the change.
I am still working on the handling of different formatted names, currently the databases I work with are all formatted the same, but the case can be anyway.
GJ

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-08 : 13:09:07

It's been my experience that when names are formatted like this there is always a 'Thornton, billy bob' or 'de la hoya,oscar' just to throw a wrench in things!

Jim

gj0519
Starting Member

13 Posts

Posted - 2008-07-08 : 13:39:14

I was incorrect on my last post about the name formatting, sometimes there will be a middle initial. So I am going to have to figure out how to handle those names.

Ex. FISHER, HANNA M.
My function currently returns: Fisher,Hanna m.

GJ

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-08 : 14:17:04

check this out too:

Parse and format name strings.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources