I came up with this: It's a set based approach, rather than working all the way through the string. Check for any strings which are suspect then fix the first instance within the string. I also used a DONE flag so as not to hit every row in the table at every iteration.

Then loop round again for any rows which still have an instance of the suspect string. I tried it with some bodged data from AdventureWorksDW..DimCustomer (18,000+ rows) and it only took 4 seconds.

-- CREATE TEST DATA

drop table ImportDataFilter

go

Create Table ImportDataFilter (MyID Int Identity(1,1) NOT NULL, Location varchar(100), Comments varchar(20))

GO

Insert into ImportDataFilter (Location, Comments)

(Select Upper('Mandarin'),'' UNION ALL Select Upper('San Jose'),'' UNION ALL Select Upper('Baymeadows'),'' UNION ALL

Select Upper('My FH Locale'),'' UNION ALL Select Upper('St. Augustine'),'' UNION ALL Select Upper('Test For Three Spaces'),'Concatenated Spaces'

UNION ALL Select Upper('Test for being Four Spaces'),'Concatenated Spaces' UNION ALL Select Upper('Test for being Five More Spaces '),'Concatenated Spaces'

UNION ALL Select Upper('Baymeadows'),'' UNION ALL Select Upper('St. Augustine'),'' )

-- test with leading space

UNION ALL Select Upper(' Oswestry'),''

GO

If (Select Object_ID('tempdb..#MyTemp2')) is NOT NULL

Drop table #MyTemp2; -- Drop temp table if it already exists

Select Distinct Location into dbo.#MyTemp2 from dbo.ImportDataFilter; --Get distinct values for all locations

Update mt1 Set Location = Lower(mt1.Location) from dbo.#MyTemp2 mt1; -- Set entire string to lower case letters

-- Brandie's article mentioned double spaces - this will do the trick

-- Now get rid of conatenated spaces

-- simply loop while there are still instances of SPACESPACE

Declare @spFlag int , @debugcount int

select @spFlag = 1, @debugcount = 0

while @spFlag <> 0

BEGIN

Update #MyTemp2 set Location = replace(Location, ' ',' ')

-- we only want to do the rows affected and not the whole table every time

where charindex(' ', Location) > 0

-- how many rows are left : if zero then quit

Select @spFlag = count(*) from #MyTemp2 where charindex(' ', Location) > 0

-- just in case we spiral out of control

Select @debugcount = @debugcount +1

if @debugcount = 100

SELECT @spFlag = 0 -- just in case

-- print @debugcount -- debug

END

-- select * from #MyTemp2

-- ===================================================================

-- Now for the title case

-- First: do the Very First char: a blanket approach for ease

Update #MyTemp2 set Location = upper(left(Location,1)) + substring(Location,2,300)

-- add a column to record the position of the next space which needs attention

Alter table #MyTemp2 add spacepos int, DONE int

go

-- initialise

update #MyTemp2 set spacepos = charindex(' ',Location), DONE = 0

-- NOW LOOP

Declare @ucFlag int

select @ucFlag = 1

WHILE @ucFlag <> 0

BEGIN

-- process the Location

UPDATE #MyTemp2 SET

Location = LEFT(Location, spacepos) + upper(substring(Location,spacepos + 1,1)) + substring(Location,spacepos + 2,250)

WHERE spacepos <> 0 and DONE = 0

-- get new spacepos

UPDATE #MyTemp2 SET spacepos = charindex(' ',Location, spacepos + 1)

WHERE spacepos <> 0 and DONE = 0

SELECT @ucFlag = count(*) from #MyTemp2 where spacepos <> 0

-- are any spacepos now = 0 ?

-- if so then set DONE

UPDATE #MyTemp2 SET DONE = 1

where spacepos = 0 and DONE = 0

END

select * from #MyTemp2

-- then you can update your original table