Similar to some of the posts above - here's my version using a function. It performs well.

Here's the function:

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

Then apply it as follows (using the table name from the original post):

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