Add line break character for a long string (word wrapping)

Transact-SQL
1

I need to add line break character to a long string (word wrapping) so that each line should not contain more than 20 symbols, divided by words (with space symbol).
For example:

input: 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3
output: 1 2 3 4 5 6 7 8 9 0 \n1 2 3 4 5 6 7 8 9 0 \n1 2 3 4 5 6 7 8 9 0 \n 1 2 3

Here is my query which is almost done.
But something is wrong with it.

For input: 111111 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3
It gives me wrong result: 111111 2 3 4 5 6 7 8 9 0 1 \n2 3 4 5 6 7 8 9 0 1 \n2 3 4 5 6 7 8 9 0 1 \n2 3

As you can see first line is more than 20 symbols.
I can't find how to tune it.
Can you suggest me how to enhance my query?

;WITH ct AS (
	SELECT 
		'1111111 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0' AS VAL, 
		20 AS MAX_LINE
),PT AS (
	SELECT 
		VAL,
		1 AS LVL,
		0 AS LEN_LINE,
		0 AS LEN_PART,
		MAX_LINE,
		VAL + ' ' AS REST, 
		CAST('' AS NVARCHAR(300)) AS LINES
	FROM CT
	UNION ALL
	SELECT 
		S.VAL,
		S.LVL,
		CASE WHEN S.LEN_LINE + S.LEN_PART > S.MAX_LINE THEN S.LEN_PART ELSE S.LEN_LINE + S.LEN_PART END AS LEN_LINE,
		S.LEN_PART,
		S.MAX_LINE,
		S.REST,
		CAST(S.LINES + CASE WHEN S.LEN_LINE + S.LEN_PART > S.MAX_LINE THEN '\n' ELSE '' END AS NVARCHAR(300)) AS LINES
	FROM (
		SELECT 
			S.*,
			CHARINDEX(' ', S.REST) AS LEN_PART
		FROM (
			SELECT 
				P.VAL,
				P.LVL + 1 AS LVL,
				P.LEN_LINE,
				P.MAX_LINE,
				RIGHT(P.REST, LEN(P.REST + '_')-1-CHARINDEX(' ', P.REST)) AS REST,
				P.LINES + LEFT(P.REST, CHARINDEX(' ', P.REST)) AS LINES
			FROM PT AS P
			WHERE CHARINDEX(' ', P.REST) > 0
		) AS S
	) AS S
)
--uncomment for debug
--SELECT * FROM PT

SELECT S.*
FROM (
	SELECT 
		P.VAL,
		P.LVL,
		P.LEN_LINE,
		P.LEN_PART,
		P.REST,
		CASE WHEN RIGHT(P.LINES, 2) = '\n' THEN LEFT(P.LINES, LEN(P.LINES + '_')-1-2) ELSE P.LINES END AS LINES, 
		ROW_NUMBER() OVER(PARTITION BY P.VAL ORDER BY P.LVL DESC) AS N
	FROM PT AS P
) AS S
WHERE S.N = 1
3

Your link doesn't help to solve my problem.

4

hi hope this helps

declare @string varchar(100) = '1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3'

select STRING_AGG ([value],'0 \n')  from string_split(@string,'0')

image
image722×297 3.13 KB

5

This string literal with zeroes is a simple example to show you the issue.
What if the string will contain different words taken from a table column?

6

ok i worked on this
hopefully this will solve your issue
it might have to be tweaked a little bit to suit your table name etc

you have to create the function below .. please click on the arrow mark

create function script

create function SplitString
(
@str varchar(max),
@length int
)
RETURNS @Results TABLE( Result varchar(50),Sequence INT )
AS
BEGIN

DECLARE @Sequence INT
SET @Sequence = 1

DECLARE @s varchar(50)
WHILE len(@str) > 0
BEGIN
    SET @s = left(@str, @length)
    INSERT @Results VALUES (@s,@Sequence)

    IF(len(@str)<@length)
    BREAK

    SET @str = right(@str, len(@str) - @length)
    SET @Sequence = @Sequence + 1
END
RETURN

END

sample data script

declare @table table ( id int,string varchar(100))

insert into @table
select 1,'sdgjongogsojndnjdhbhondeonhboqnhqoeh'
union all
select 2,'qwerf4467gfghjjjjjjjsreyr6r624u24ujtj3j35j3j3j3j146161areahh'
union all
select 3,'q2w3e4r5t6y6SAGGWHWDHDHHHQJQ4TRJWTJKWTJKWWKW5YK'

;with cte as 
( select id,result FROM @table  AS PP CROSS APPLY SplitString(PP.string,10) 
) 
SELECT   
     id
   , STRING_AGG(result, '\n') WITHIN GROUP (ORDER BY id ASC) AS string
 FROM 
    cte
 GROUP BY 
    id;

image

7

Do you know how word wrapping works?
I can't split a word in two different parts (even if it is more than 10 symbols).
I need to split them only by space.
It was done in my initial script and I mentioned it my first message.

8

i am not able to understand you

doing the SQL to get the desired result
is extemely easy

i need to understand exactly what you are saying

please help me understand

how to convey what you want to other people
is very very very common in each and everything
it has to be easy to understand or you make it easy to understand
thats a PHD subject by itself

its nothing personal against you
please dont take it the wrong way

just my 2 cents

:+1: :+1: