Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - 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')


ċ
Convert string to ProperCase.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments