November 4, 2025 at 7:43 pm
I have an issue with a varchar(max) field with up to 7000 characters needing to be put in to lines of 85 characters long and keep formatting with char(13) in it and not to break words. Basically it is to take a memo field from a software to import into Business Central Extended Text lines. Any help Greatly appreciated.
November 5, 2025 at 11:28 am
third attempt, without the code: if i paste long replies with lots of code, whether i put it in a code box or not, my reply to the post disappears.
the thread below has code that still works:
oh man, I saved this exact kind of solution from March 2009. 16 years later, the question popped up again.
so if the text has a CrLf, it is preserved as a separate line. . word break at at most 85 characters. the example here is a table with recipes.
http://www.sqlservercentral.com/Forums/Topic677895-338-1.aspx
Lowell
November 6, 2025 at 7:38 pm
I thought it was more complicated than that because a CHAR(13) should also count as a "space" as far as splitting words?! Perhaps not, I guess it depends on exactly what you want/need.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 7, 2025 at 3:14 am
Summary
+ Input: long varchar text; max line width W.
+ Walk left-to-right, one word at a time.
+ Separators: space; CR/LF are hard line breaks.
+ For each word:
+ Find next separator (space/CR/LF).
+ Get word text and length.
+ If it fits on the current line (plus a leading space if needed), keep it; otherwise start a new line.
+ If CR/LF, force a new line.
+ Skip runs of spaces.
+ Aggregate words per line; output wrapped lines (<= W chars).
Implementation notes
+ Single recursive CTE (no REPLACE, no STRING_SPLIT).
+ Uses LEAST (SQL Server 2022+) to pick earliest separator
+ Tracks: position, line number, current line length, word order.
DECLARE @Text varchar(max) = '---0017720610---04---04---03/11/2009--- You can check your order status, track your order, obtain authorization and instructions to return or exchange a product, get a duplicate invoice, access technical support contact information and mail in rebate forms and instructions 24 hours a day on our web site at http://www.******.ca';
DECLARE @W int = 85;
WITH r AS (
-- pos: current index into @Text (1-based)
-- line_no/cur_len: current wrapping state
-- tok_ord: token order across the whole text
-- tok: current emitted word token (NULL for hard breaks)
SELECT pos = COALESCE(NULLIF(PATINDEX('%[^ ]%', @Text), 0), LEN(@Text) + 1),
line_no = 1,
cur_len = 0,
tok_ord = 0,
tok = CAST(NULL AS varchar(max))
WHERE LEN(@Text) > 0
UNION ALL
SELECT
-- advance position:
pos = CASE WHEN cur.c1 IN (CHAR(13), CHAR(10)) THEN COALESCE(NULLIF(CASE WHEN SUBSTRING(@Text, r.pos, 2) = CHAR(13)+CHAR(10) THEN r.pos + 2 ELSE r.pos + 1 END, 0), LEN(@Text) + 1)
ELSE
-- jump to next separator end, then skip spaces
CASE WHEN nxt.rawNext > LEN(@Text) THEN LEN(@Text) + 1
ELSE
CASE WHEN nxt.skipOff = 0 THEN nxt.rawNext
ELSE (nxt.rawNext - 1) + nxt.skipOff END END END,
-- line number:
line_no =
CASE
WHEN cur.c1 IN (CHAR(13), CHAR(10)) THEN r.line_no + 1
WHEN r.cur_len = 0 THEN r.line_no
WHEN r.cur_len + 1 + nxt.tokLen > @W THEN r.line_no + 1
ELSE r.line_no
END,
-- current line length after placing token or break:
cur_len =CONVERT(int,
CASE
WHEN cur.c1 IN (CHAR(13), CHAR(10)) THEN 0
WHEN r.cur_len = 0 THEN nxt.tokLen
WHEN r.cur_len + 1 + nxt.tokLen > @W THEN nxt.tokLen
ELSE r.cur_len + 1 + nxt.tokLen
END),
-- token order increments only when emitting a word
tok_ord = CASE WHEN cur.c1 IN (CHAR(13), CHAR(10)) THEN r.tok_ord ELSE r.tok_ord + 1 END,
-- emitted token (NULL for hard breaks)
tok = CASE WHEN cur.c1 IN (CHAR(13), CHAR(10)) THEN NULL
ELSE SUBSTRING(@Text, r.pos, nxt.tokLen) END
FROM r
CROSS APPLY (SELECT SUBSTRING(@Text, r.pos, 1) AS c1) cur
CROSS APPLY (-- find next separator (space, CR, LF) at/after pos
SELECT
sp = NULLIF(CHARINDEX(' ', @Text, r.pos), 0),
cr = NULLIF(CHARINDEX(CHAR(13), @Text, r.pos), 0),
lf = NULLIF(CHARINDEX(CHAR(10), @Text, r.pos), 0)) sep
CROSS APPLY (SELECT firstSepPos = LEAST(sep.sp, sep.cr, sep.lf)) firsts
CROSS APPLY (-- tokLen: length of word until next separator
SELECT tokLen = CASE
WHEN cur.c1 IN (CHAR(13), CHAR(10)) THEN 0
WHEN firsts.firstSepPos IS NULL THEN LEN(@Text) - r.pos + 1
ELSE firsts.firstSepPos - r.pos
END) lens
CROSS APPLY (-- rawNext: position right after the token (or end of string)
SELECT rawNext = COALESCE(firsts.firstSepPos, LEN(@Text) + 1)) next1
CROSS APPLY (-- skipOff: offset to first non-space after rawNext using PATINDEX
SELECT skipOff = COALESCE(
NULLIF(PATINDEX('%[^ ]%', SUBSTRING(@Text, next1.rawNext, LEN(@Text) - next1.rawNext + 1)), 0), 0)) skip
CROSS APPLY (SELECT tokLen = lens.tokLen, rawNext = next1.rawNext, skipOff = skip.skipOff) nxt
WHERE r.pos <= LEN(@Text))
SELECT STRING_AGG(tok, ' ') WITHIN GROUP (ORDER BY tok_ord) clipped_string,
len(STRING_AGG(tok, ' ') WITHIN GROUP (ORDER BY tok_ord)) len_clipped_string
FROM r
WHERE tok IS NOT NULL
GROUP BY line_no
ORDER BY line_no
OPTION (MAXRECURSION 32767);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 7, 2025 at 2:12 pm
To all: on Vacation till mid next week. Thanks for all the replies and will look at them when I return from vacation!
November 17, 2025 at 2:22 pm
Thanks to all
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply