This is the code we have been using to do this, it's almost certainly not the best way of doing it but it works

DECLARE @index int,@Char CHAR(1),@InputString VARCHAR(255), @OutputString VARCHAR(255),@postlength int, @postcode char (10)
SET @InputString = 'THE SQUAT, END OF MY ROAD OTHER PLACE, LONDON NX1 2BD'

select @postcode = ltrim(right(rtrim(@InputString),8))
if @postcode <> '' and right(left(@postcode,3),1) not like '[0-9]'
select @postcode = '', @postlength = 0
else if right(left(@postcode,4),1) not like '[0-9]'
select @postlength = 8
else
select @postlength = 9

select @outputstring = LOWER(substring(@InputString,1,len(@InputString)-@postlength))
SET @index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@OutputString,1,1)))

WHILE @index <= LEN(@InputString)-@postlength
BEGIN
SET @Char = SUBSTRING(@InputString, @index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''', '(')
IF @index + 1 <= LEN(@InputString)-@postlength
BEGIN
IF @Char != ''''
SET @OutputString =
 STUFF(@OutputString, @index + 1, 1,UPPER(SUBSTRING(@InputString, @index + 1, 1)))
END
SET @index = @index + 1
END

select locaddress1 =
CASE WHEN @postlength > 0 then
ISNULL(@OutputString+(right(@InputString,@postlength)),'')
ELSE
ISNULL(@OutputString,'')
END