UpperCase Character after hyphenated name

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)

UpperCase Character after hyphenated name

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2009-07-02 : 06:18:10

Hello

I have a field containing a persons surname which was originally all in upper case. I have written the below code to keep the first character in the field as upper case and the rest of the surname as lower case which works okay:

(SUBSTRING(SURNAME,1,1)) + LOWER(SUBSTRING(SURNAME,2,LEN(SURNAME)))

I have noticed a couple of names which are hyphenated and now appear like: Wallace-dand.

From the above example how can i get the starting d in dand to become an uppercase D leaving the name looking like Wallace-Dand?

Thanks you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 12:49:07

you can use like this

SELECT COALESCE(LEFT(NamePart1,1) + LOWER(SUBSTRING(NamePart1,2,LEN(NamePart1))) + '-','') + LEFT(NamePart2,1) + LOWER(SUBSTRING(NamePart2,2,LEN(NamePart2)))
FROM 
(SELECT PARSENAME(REPLACE(SURNAME,'-','.'),2) AS NamePart1,
PARSENAME(REPLACE(SURNAME,'-','.'),1) AS NamePart2
FROM YourTable
)t

however i feel its best to do this at front end as this is a poresentation issue and you've lots of formatting functions which can leverage upon for this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources