Use Uppercase within spaced last names such as 'Mc Donnald'
Posted on 2010-01-07
Question: I have been having a hard time trying to figure out how to capitalize the second portion of a last name that has a space in it such as the capitalization of the last name 'Mc donnald'. I would like the d within 'Donnald' capitalized as such. Here is a snippet of my code and a few lines within the result set:
SELECT TOP 100 PERCENT CASE WHEN dbo.tblemployees.nickname <> '' THEN CONVERT(varchar(50), upper(substring(dbo.tblemployees.nickname, 1, 1))
+ lower(substring(dbo.tblemployees.nickname, 2, 49))) ELSE CONVERT(varchar(50), upper(substring(dbo.tblemployees.fname, 1, 1))
+ lower(substring(dbo.tblemployees.fname, 2, 49))) END AS [First Name], CONVERT(varchar(50), UPPER(SUBSTRING(dbo.tblEmployees.lname, 1, 1))
+ LOWER(SUBSTRING(dbo.tblEmployees.lname, 2, 49))) AS [Last Name], dbo.lkpDepartments.Department, dbo.tblEmployees.ext AS [Ext.],
dbo.vw_ADSI.mail AS [Work Email]
Joan Mc call Warehouse NULL
Sally Mc donald Warehouse NULL
Ryan Mc elheny Warehouse NULL
Janelle Mc garry Information Technology 315 Janelle.McGarry@sasinc.com
Any help would be greatly appreciated!