Why nobody likes my solution without UDF, WHILEs, Dynamic SQL statments, etc ...
🙂
Create Table #Import_Data_Filter (MyID Int Identity(1,1) NOT NULL, Location varchar(100))
Insert into #Import_Data_Filter (Location)
(Select Lower('Mandarin') UNION ALL Select Lower('San Jose') UNION ALL Select Lower('Baymeadows') UNION ALL
Select Lower('My FH Locale') UNION ALL Select Lower('St. Augustine') UNION ALL Select Lower('Test For Three Spaces')
UNION ALL Select Lower('Test for being Four Spaces') UNION ALL Select Lower('Test for being Five More Spaces')
UNION ALL Select Lower('Baymeadows') UNION ALL Select Lower('St. Augustine'))
update #Import_Data_Filter set
location = char(160)+replace(location,' ',char(160))
r:
update #import_data_filter set
location = replace(
location,
char(160)+substring(location,charindex(char(160),location)+1,1),
' '+UPPER(substring(location,charindex(char(160),location)+1,1))
)
where charindex(char(160),location)<>0
if @@rowcount>0 goto r
select ltrim(location) from #Import_Data_Filter
drop table #Import_Data_Filter