Here's a recursive CTE method as a iTVF that could work.

I would like to come up with a none recursive method, but brain fade is setting in and I'm not seeing a way to do it currently :crying:

One thing to note is that it will break if there is a word longer than the @maxlen specified. The character at that point will be replaced.

CREATE FUNCTION splitAt(@str varchar(8000), @maxlen int, @splitOn varchar(1), @replaceWith varchar(1)) RETURNS TABLE AS

RETURN

WITH rcte as (

SELECT substring(@str,0,@maxlen - charindex(@spliton,reverse(substring(@str,0,@maxlen)))) s,

substring(@str,@maxlen - charindex(@spliton,reverse(substring(@str,0,@maxlen))) + 1,8000) r

UNION ALL

SELECT substring(r,0,@maxlen - charindex(@spliton,reverse(substring(r,0,@maxlen)))) s,

substring(r,@maxlen - charindex(@spliton,reverse(substring(r,0,@maxlen))) + 1,8000) r

FROM rcte

WHERE len(r) > 0

)

SELECT SUBSTRING(S,2,8000) splitResult

FROM (

SELECT @replacewith + s AS [text()]

FROM rCte

ORDER BY LEN(r) DESC

FOR XML PATH ('')) A (S)

On my machine with 10000 address rows with an average length of 55 (max 121, min 21) it took around a second.