-- 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 

use tempdb
go
Create Table Import_Data_Filter (MyID Int Identity(1,1) NOT NULL, Location varchar(100))
GO
Insert into Import_Data_Filter (Location)
(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')
UNION ALL Select Upper('Test for being Four Spaces') UNION ALL Select Upper('Test for being Five More Spaces') 
UNION ALL Select Upper('Baymeadows') UNION ALL Select Upper('St. Augustine'))
GO

use tempdb
go
BEGIN TRAN;

UPDATE	IDF
SET	Location = Import_Data_Fixed.LocationMixed
FROM	Import_Data_Filter as IDF
JOIN	(select	MyID
	,	UPPER(Substring(Location,1,1)) -- First Character is always UPPER CASE
	+	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
		(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
		(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		LOWER(SUBSTRING(Location,2,100))
		, ' a', ' A'), ' b', ' B'), ' c', ' C'), ' d', ' D'), ' e', ' E'), ' f', ' F')
		, ' g', ' G'), ' h', ' H'), ' i', ' I'), ' j', ' J'), ' k', ' K'), ' l', ' L')
		, ' m', ' M'), ' n', ' N'), ' o', ' O'), ' p', ' P'), ' q', ' Q'), ' r', ' R')
		, ' s', ' S'), ' t', ' T'), ' u', ' U'), ' v', ' V'), ' w', ' W'), ' x', ' X')
		, ' y', ' Y'), ' z', ' Z')
			as LocationMixed
		from	Import_Data_Filter
		) AS Import_Data_Fixed
		on	Import_Data_Fixed.MyID	= IDF.MyID
		AND Import_Data_Fixed.LocationMixed <> IDF.Location
;		
select * from Import_Data_Filter;
rollback;

CREATE FUNCTION [dbo].[ToProperCase](@string NVARCHAR(4000)) RETURNS NVARCHAR(4000) 
AS
BEGIN
		
	SET @string = LOWER(LTRIM(RTRIM(@string)))
	
	DECLARE @i INT
	SET @i = ASCII('a')
	
	WHILE @i <= ASCII('z')
	BEGIN
	
		SET @string = REPLACE( @string, ' ' + CHAR(@i), ' ' + CHAR(@i-32))
		SET @string = REPLACE( @string, '-' + CHAR(@i), '-' + CHAR(@i-32))
		SET @i = @i + 1
	END
	
	IF (ASCII(LEFT(@string, 1)) BETWEEN ASCII('a') AND ASCII ('z'))
	BEGIN
		SET @string = CHAR(ASCII(LEFT(@string, 1))-32) + RIGHT(@string, LEN(@string)-1)
	END 
	
	RETURN @string
END

CREATE FUNCTION [CapitalCase]  
(  
        @Input varchar(255)  
)  
RETURNS varchar(255)  
AS  
BEGIN  
        DECLARE @Results varchar(255)  
  Declare @NextWord int, @NextSpace int  
        if len(@Input)>0  
                Begin        
      -- Trimming the input  
                        Set @Input = lower(ltrim(rtrim(@Input)))                 
                        -- Finding next word in the input  
                        Set @NextWord = patindex('%[a-zA-Z]%',@Input)  
                        -- Initializing the result   
                        Set @Results = ''  
                        -- If there is a word beginning, then make it Capital case  
                        While @NextWord <> 0  
                        Begin  
                                -- Capital casing first character of first word and adding to result  
                                Set @Results = @Results + UPPER(substring(@Input,@NextWord,1))  
                                -- finding next word from next space  
        Set @NextSpace = charindex(char(32),@Input,1)  
        -- If there is no space then words are over, write till the end of string to result and break out of the loop  
        If @NextSpace = 0  
        BEGIN  
         SET @Results = @Results +SUBSTRING(@Input,@NextWord+1,LEN(@Input))  
         BREAK  
        END  
        -- If space is present then there might be one more word existing  
        ELSE  
        BEGIN  
            -- Set result set from the capital case character till the next space   
         SET @Results = @Results + SUBSTRING(@Input,@NextWord+1,(@NextSpace - @NextWord))  
         -- Making input as remaining part of Input from the next space   
         Set @Input = SUBSTRING(@Input,@NextSpace+1,LEN(@Input))  
         Set @NextWord = patindex('%[a-zA-Z]%',@Input)                                           
                                END                                 
                        End                                                 
                End  
        Else  
                Set @Results= ''  
  
        Return @Results  
END  

create function uf_mixed (@p_col varchar(max))
returns varchar(max) 
as
begin
declare @lgth int, @ctr int, @found varchar(1), @v_col varchar(max), @nxt int

select @lgth = LEN(@p_col), @ctr = 1, @found = 'x', @nxt = 0

if @lgth > 0
begin
select @v_col = upper(SUBSTRING(@p_col,1,1))
select @ctr = @ctr + 1

while (@ctr <= @lgth)
begin
	select @found = SUBSTRING(@p_col,@ctr,1)
	if @found = ' ' select @nxt = @ctr+1
	if @nxt = @ctr
		select @v_col = @v_col + upper(SUBSTRING(@p_col,@ctr,1))
	else
		select @v_col = @v_col + lower(SUBSTRING(@p_col,@ctr,1))
	select @ctr = @ctr + 1 
end
end
return(@v_col)
end
go

update Import_Data_Filter set Location = dbo.uf_mixed(Location)

CREATE FUNCTION   dbo.fn_title_case
(
 @string          varchar(max)
)
RETURNS           varchar(max)
AS
BEGIN

DECLARE @curr           char(1);
DECLARE @len            int;
DECLARE @loc            int;
DECLARE @out_string  varchar(max);
DECLARE @prev_alpha char(1);

SET @out_string      =  '';
SET @prev_alpha     =  'N'
SET @len               =  LEN(@string);
SET @loc               =  1;

WHILE @loc <= @len
BEGIN
  SET @curr = SUBSTRING(@string,@loc,1);

  IF @curr LIKE '[A-Z0-9]' 
  BEGIN
    IF @prev_alpha = 'Y'
      SET @out_string = @out_string + LOWER(@curr);
    ELSE
      SET @out_string = @out_string + UPPER(@curr);

    SET @prev_alpha = 'Y';
  END

  ELSE 
  BEGIN
    SET @out_string = @out_string + @curr;
    SET @prev_alpha = 'N';
  END 

  SET @loc = @loc + 1;
END                                                   

RETURN @out_string;

END

SELECT dbo.fn_decapitalise(Location) into #result from Import_Data_Filter;