Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Proper Case A String Or Name

By David Allen-Williams,

SQL 2005 user defined function to convert a string to proper case (title case).

Flexible handling of delimiters and apostrophes to cater for words like "hasn't" and also names like "O'Reilly" and "d'Angelo".

Mac and Mc surnames are handled generically, with some rules and exceptions which may need to be extended.  Since McIntyre and Mcintyre are both valid capitalisations, the input string capitalisation is preserved where capitalisation cannot be conclusively determined as incorrect.  Credits to Kim Ryan's NameParse for Mac and Mc exceptions.

This rule could also apply for other family name variants like "d'Angelo" and "D'Angelo" but it has not been applied in this version.

To use in SQL 2000, replace nvarchar(max) with unicode or non-unicode limited length string, and remove semi-colons within the script.

Total article views: 323 | Views in the last 30 days: 4
Related Articles

Sorting out capitalisation

Any advice appreciated please. In many of our reports, some text comes through in upper case, eg ...


string Comparison.

'string' = 'string___' ???


Manipulate String

String Output


csv string

csv string


string formatting

String Formatting


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones