;WITH cte AS 
-- split each string by character and decide UPPER or lower based on previous character or being the first char
(
  SELECT
	N, 
	Id,
	CASE 
		WHEN N=1 OR (N>1 AND SUBSTRING(location ,N-1,1 ) = ' ') 
		THEN UPPER(SUBSTRING(location ,N ,1)) 
		ELSE LOWER(SUBSTRING(location ,N ,1)) 
	END AS split
  FROM Tally
  CROSS APPLY -- apply the code to each location
  ( SELECT  Location, min(MyId) AS Id
    FROM    Import_Data_Filter
    GROUP BY Location
  )y
  WHERE N < LEN(' ' + location + ' ')
)
-- and put it back together
SELECT
  REPLACE((SELECT '' + split FROM cte c2 WHERE c2.Id = c1.Id ORDER BY N FOR XML PATH('')),' ',' ')
FROM cte c1
GROUP BY Id

CREATE FUNCTION [dbo].[FormalCase]
(
	@Input varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
	DECLARE @Results varchar(255)

	if len(@Input)>0
		Begin
			Set @Input = lower(ltrim(rtrim(@Input)))
			Declare @NextSpace int, @LastSpace int
			Set @LastSpace = 0
			Set @NextSpace = charindex(char(32),@Input,1)
			
			While @NextSpace>@LastSpace
			Begin
				Set @Input = Left(@Input, @NextSpace) + upper(substring(@Input,@NextSpace + 1, 1)) + Right(@Input, len(@Input)-(@Nextspace+1))
				Set @LastSpace = @NextSpace
				Set @NextSpace = charindex(char(32),@Input,@LastSpace + 1)
			End

			SELECT @Results = Upper(left(@Input,1)) + right(@Input, len(@Input)-1)
		End
	Else
		Set @Results= ''

	RETURN @Results
END
/* UNIT TESTING
Select dbo.formalcase('a stitch in time')
*/
GO

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

ALTER FUNCTION [dbo].[fnProperCase] 

 
(
 @String varchar(1023)
)  
 
RETURNS varchar(1023)
 
AS  
 
BEGIN 
 
 DECLARE @Pos int,
  @Temp varchar(1023),
  @Result varchar(1023)
 
 SET @Temp = LOWER(LTRIM(RTRIM(@String)))
 SET @Result = UPPER(SUBSTRING(@Temp,1,1))
 SET @Temp = SUBSTRING(@Temp,2,1022)
 SET @Pos = PATINDEX('%[ ,''-][a-z]%', @Temp)
 WHILE @Pos > 0
  BEGIN
   SET @Result = @Result + SUBSTRING(@Temp,1,@Pos) + UPPER(SUBSTRING(@Temp,@Pos+1,1))
   SET @Temp = SUBSTRING(@Temp, @Pos + 2, 1022)
   SET @Pos = PATINDEX('%[ ,''-][a-z]%', @Temp)
  END
 SET @Result = @Result + @Temp
 
 RETURN @Result
 
END

Update idf
Set Location = mt1.Location
from dbo.Import_Data_Filter idf
join dbo.#MyTemp1 mt1
on UPPER(idf.Location) = UPPER(mt1.Location);

ALTER FUNCTION [cf_ProperCase] (@String varchar(MAX))
RETURNS varchar(MAX)
AS BEGIN

    DECLARE @ReturnString varchar(max),
            @i int;

    SET @ReturnString = ''
    SET @i = 1

    WHILE @i <= LEN(@String)
        BEGIN
            SET @ReturnString = @ReturnString +
                (CASE WHEN (@i = 1 OR SUBSTRING(@String, @i - 1, 1) = ' ')
                    THEN UPPER(SUBSTRING(@String, @i, 1))
                    ELSE LOWER(SUBSTRING(@String, @i, 1))
                    END)
            SET @i = @i + 1
        END

    RETURN @ReturnString


END

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER   procedure sp_ProperCase
@table varchar(50),@field varchar(50)
as

-- Replace spaces with the "special" character
exec('update   ' + @table + '  
 set   ' + @field + '   = lower(replace(   ' + @field + '  , '' '', ''@''))')


-- Handle case 1 - First item
exec('update   ' + @table + '  
 

set   ' + @field + '   = upper( substring( ltrim(   ' + @field + '  ), 1, 1)) + substring( ltrim(   ' + @field + '  ), 2, 80)')

-- loop while there are rows with the flag
exec('while exists(
	select *
	 from   ' + @table + '  
	 where   ' + @field + '   like ''%@%''
	)
 begin
    -- Proper case the word after the flag.
	update   ' + @table + '  
	 set   ' + @field + '   = substring(   ' + @field + '  , 1, charindex( ''@'',   ' + @field + '  )) + 
		upper( substring(   ' + @field + '  , charindex( ''@'',   ' + @field + '  )+1, 1 )) + 
		substring(  ' + @field + '  , charindex( ''@'',   ' + @field + '  )+2, 80)
	 where   ' + @field + '   like ''%@%''
	-- Remove the first flag encountered in each row
	update   ' + @table + '  
	 set   ' + @field + '   = substring(   ' + @field + '  , 1, charindex( ''@'',   ' + @field + '  )-1) +
		'' '' + substring(   ' + @field + '  , charindex( ''@'',   ' + @field + '  ) + 1, 80)
	 where   ' + @field + '   like ''%@%''
 end')

exec('update ' + @table + ' set ' + @field + ' = replace(' + @field + ','' ii'','' II'') where ' + @field + ' like ''% ii''')
exec('update ' + @table + ' set ' + @field + ' = replace(' + @field + ','' iii'','' III'') where ' + @field + ' like ''% iii''')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO