SQL - Capitalize only the first letter of each word and lowercase all the rest

Question

I've seen this posted everywhere and I can't seem to find a realistic solution for my situation. In our Address field, we have values such as "555 RODeo drive, 555 rodeo drive, 555 RODEO DRIVE, etc. These need to be formatted as 555 Rodeo Drive. Please advise on a way to do this that can also be used on the city, state and country fields. i.e. UNITED STATES > United States.

Using SQL Server to turn something into "Propercase" is ill-advised in my opinion; SQL Server's forté is not string manipulation. Use something that is far better at doing it, like your presentation layer. For example, if you're using a .Net language, you have the function `ToTitleCase`. — Thom A, Oct 07 '19 at 13:24
Yes - it is posted everywhere and none are perfect. There's a reason for that - you can't write code to compensate for every possibility of flawed human input. — SMor, Oct 07 '19 at 13:25
Take a peek at https://stackoverflow.com/questions/54239873/i-want-to-update-values-of-a-column-in-a-table-to-title-case/54240287#54240287 — John Cappelletti, Oct 07 '19 at 13:29

score 0 · Answer 1 · answered Oct 07 '19 at 13:36

This kind of work best to do it in the presentation layer, where you can use a Mask when the data entered, and also update those strings, but here is a tip

SELECT STRING_AGG(S, ' ')
FROM
(
  VALUES
  (1, '555 RODeo drive'), 
  (2, '555 rodeo drive'), 
  (3, '555 RODEO DRIVE'),
  (4, 'UNITED STATES')
) T(ID, Str) CROSS APPLY
(
  SELECT CONCAT(UPPER(LEFT(Value, 1)), LOWER(RIGHT(Value, LEN(Value) -1))) S
  FROM STRING_SPLIT(Str, ' ')
) TT
GROUP BY ID;

Online Demo

score 0 · Answer 2 · answered Oct 07 '19 at 13:41

0

The easiest way to accomplish this is using Fuzzy Lookup in SSIS. A sample implementation of Fuzzy lookup using SSIS.

But if you really want to use T-SQL, this post might help...

answered Oct 07 '19 at 13:41

VB_isYoung

83
8

score 0 · Answer 3 · edited Aug 31 '22 at 23:37

A variation of similar logic Ilyes posted. SQL Server 2016 and greater.

CREATE OR ALTER FUNCTION [Reference].[TitleCase](@String NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @Result NVARCHAR(2000)

SET @Result = (SELECT STRING_AGG(Agg.IndividualWords,' ')
                    FROM
                        (
                             SELECT IndividualWords = 
   
   
   
 CONCAT(UPPER(SUBSTRING(Split.value,1,1)),LOWER(SUBSTRING(Split.value,2,200)))
                             FROM STRING_SPLIT(@String,' ') Split
                        ) Agg
               )
    RETURN @Result
END
GO


DECLARE @String VARCHAR(100) ='Test t st length here is some more jiiberish test all you want 123 & * # < , . .' + CHAR(0x0) + CHAR(0x3)

SELECT  [Reference].[TitleCase] (@String)

Result: Test T St Length Here Is Some More Jiiberish Test All You Want 123 & * # < , . .

SQL - Capitalize only the first letter of each word and lowercase all the rest

3 Answers3