Convert String to Proper Case

Applicability:

SQL Server 2000: Not Supported **

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown/ChillyDBA

Date: 5 Jun 2012

Description

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.

Code

DROP FUNCTION dbo.udf_ProperCase

GO

CREATE FUNCTION DBO.udf_ProperCase(@String VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS

BEGIN

DECLARE @Proper VARCHAR(MAX)

SET @Proper = ''

SELECT

@Proper = @Proper +

CASE

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))

END

FROM udf_NumbersTable(1,LEN(@String))

WHERE Value <= LEN(@String)

RETURN @Proper

END

GO

-- USAGE:

-- SELECT dbo.udf_ProperCase('COMPANY NAME IN ALL CAPS')

-- SELECT dbo.udf_ProperCase('TESting, ONE, two, thREE')