SQL Convert Simple Names to Proper Case

Introduction

As with the original tip, this code is limited to affecting the casing of simple names -- by which I mean that you know that each and every "word" in the value should begin with one capital letter.

Background

This is submitted only as an Transact-SQL alternative. Such string manipulation should not be done in SQL, but here's how it can be done with a Recursive Common Table Expression.

Using the Code

You'll need the table you want to update. I'm using a temporary table just to test.

DROP TABLE #temp
;
SELECT *
INTO #temp
FROM
(
  SELECT 'IOWA' [foo]
UNION ALL
  SELECT 'iowa' [foo]
UNION ALL
  SELECT 'NORTH CAROLINA'
UNION ALL
  SELECT 'north carolina'
UNION ALL
  SELECT 'NEW SOUTH WALES'
UNION ALL
  SELECT 'new south wales'
) T
; 
SELECT * 
FROM #temp

First, I'm using a regular Common Table Expression just to get a list of all the distinct values after they have been made uppercase. The key is to find a pair of consecutive uppercase letters and then a SPACE following the "word". Be sure to append a SPACE to the input value to ensure that you always have a SPACE to find. I don't like having to cast to VARCHAR(MAX), so maybe someone can find a way to avoid that.

;
WITH [cte0] AS
(
  SELECT DISTINCT CAST ( UPPER ( [foo] ) + ' ' AS VARCHAR(MAX) ) [foo]
  , CAST ( PATINDEX ( '%[A-Z][A-Z]%' COLLATE Latin1_General_BIN , _
  [foo] COLLATE Latin1_General_BIN ) AS BIGINT ) [bar]
  FROM #temp
)

And here's the Recursive Common Table Expression. It repeatedly replaces the part of a "word" after the leading uppercase letter with the lowercase version until there are no more pairs of consecutive uppercase letters.

, [cte1] AS
(
  SELECT [foo]
  , [bar]
  , CHARINDEX ( ' ' , [foo] , [bar] ) [baz]
  FROM [cte0]
  WHERE [bar] > 0
UNION ALL
  SELECT [foo]
  , [bar]
  , CHARINDEX ( ' ' , [foo] , [bar] ) [baz]
  FROM
  (
    SELECT [foo]
    , PATINDEX ( '%[A-Z][A-Z]%' COLLATE Latin1_General_BIN , [foo] COLLATE Latin1_General_BIN ) [bar]
    FROM
    (
      SELECT SUBSTRING ( [foo] , 1 , [bar] ) + LOWER ( SUBSTRING ( [foo] , [bar] + 1 , _
      [baz] - [bar] - 1 ) ) + SUBSTRING ( [foo] , [baz] , DATALENGTH ( [foo] ) ) [foo]
      FROM [cte1]
      WHERE [bar] > 0
    ) T
  ) U
)

The result can then be used to UPDATE the table.

UPDATE #temp
SET [foo] = RTRIM ( B.[foo] )
FROM #temp A
INNER JOIN [cte1] B
ON A.[foo] = RTRIM ( B.[foo] )
AND B.[bar] = 0
; 
SELECT * 
FROM #temp

History

• 2017-11-19 First submission