Here is an iTVF for it...
CREATE FUNCTION SplitWithDelimiter (@MyString VARCHAR(200), @MaxLength INT, @Delimiter CHAR(1), @Breakers varchar(256))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT substring(base.value, 1, Pos1) + @Delimiter + substring(base.value, Pos1 + 1, Pos2 - Pos1) + @Delimiter + substring(base.value, Pos2 + 1, Pos3 - Pos2) + @Delimiter + substring(base.value, Pos3 + 1, Pos4 - Pos3) + @Delimiter + substring(base.value, Pos4 + 1, Pos5 - Pos4) + @Delimiter + substring(base.value, Pos5 + 1, Pos6 - Pos5) + @Delimiter + substring(base.value, Pos6 + 1, Pos7 - Pos6) AS result
FROM (
VALUES (@MyString)
) AS base(value)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, 1, @MaxLength))), (@MaxLength + 1)), @MaxLength)
) a(Pos1)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos1 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos1
) b(Pos2)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos2 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos2
) c(Pos3)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos3 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos3
) d(Pos4)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos4 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos4
) e(Pos5)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos5 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos5
) f(Pos6)
CROSS APPLY (
SELECT ISNULL(NULLIF((@MaxLength + 1) - PATINDEX('%'+@Breakers+'%', REVERSE(SUBSTRING(base.value, Pos6 + 1, @MaxLength))), (@MaxLength + 1)), @MaxLength) + Pos6
) g(Pos7)
I don't claim it is the quickest, but it is quite flexible:
Parameters:
@MyString : The data to be manipulated
@MaxLength : The maximum length of each new "chunk" of data
@Delimiter : The character used to seperate the new "chunks"
@Breakers : Pattern used to find a suitable break in the data - can be as simple as ' ' or more complicated like '[ .,?!:;)]'
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);