Date: 5 Jun 2012
This is a fairly straigthforward function to convert a string of variable case to a proper case string ie all new words start with a capital letter followed by lower case letters. A new word is defined as one that is preceded by a space.
I updated the original code in several ways:
- Converted to use the udf_NumbersTable instead of a dedicated lookup table
- Converted to use the new VARCHAR(MAX) data type so that it will accept longer strings (although I can't think of why you would need to proper case a string longer than 8000 characters)
**NOTE: The code as it stands is not supported on SQL Server 2000. However, replacing the udf_NumbersTable function using CTE with the one that is SQL 2000 compliant (available from the same link), and replacing VARCHAR(MAX) references with VARCHAR(8000) should enable it to function.
DROP FUNCTION dbo.udf_ProperCase
CREATE FUNCTION DBO.udf_ProperCase(@String VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS
DECLARE @Proper VARCHAR(MAX)
SET @Proper = ''
@Proper = @Proper +
WHEN Value = 1 THEN UPPER(SUBSTRING(@String, Value, 1))
WHEN Value > 1 AND SUBSTRING(@String, Value -1, 1) = ' ' THEN UPPER(SUBSTRING(@String, Value, 1))
ELSE LOWER(SUBSTRING(@String, Value, 1))
WHERE Value <= LEN(@String)
-- SELECT dbo.udf_ProperCase('COMPANY NAME IN ALL CAPS')
-- SELECT dbo.udf_ProperCase('TESting, ONE, two, thREE')