Thanks Chris and GSquared for replies and speed!

I see how those work; my concern is that using things like REPLACE(),LEN(),LEFT() on a text field of more than 8000 characters will truncate the length down to 8000 characters, meaning that I could lose data off the end of my fieldvalue; the example below will return 8000:

DECLARE @String VARCHAR(MAX) = REPLICATE('a',10000)

SELECT LEN(@String)

I am using SQL Server 2008 R2, so the VARCHAR(MAX) should be able to hold something like 2^31 characters

Edit:

Wow - you post faster than I do! I hear you about the need for sample data - taken on board. Unfortunately, we are using a database built by an external software company and have no real control over the format.