0

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.

th3_fix3r
  • 1
  • 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`. – Larnu 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
  • 2
    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

2 Answers2

0

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

Ilyes
  • 14,367
  • 4
  • 24
  • 50
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...

VB_isYoung
  • 93
  • 8