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