SQL Clone
SQLServerCentral is supported by Redgate
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 http://cpan.uwinnipeg.ca/htdocs/Lingua-EN-NameParse/Lingua/EN/NameParse.pm.htm 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: 328 | 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