Split long text without cutting words

Omar.Dweik · #1 (**permalink**) 12-20-11, 16:01

Dear All
I have used your forum for many years for tips & tricks but today I stumbled upon an issue that I couldn’t find a solution for

I have text such as ‘XL500 KOJI – This item is the best item in the market you can’t find anything else like it 500HP amazing’

Where the “XL500 KOJI” is the item Code and the rest of the text is the name,
unfortunately my report engine doesn’t support “Can grow” and hence the text is long –in this case around 104, it can be around 250- it got truncated in the print out,
I can split the text to 50 characters each but the result would be
‘XL500 KOJI – This item is the best item in the mar’
‘ket you can’t find anything else like it 500HP ama’
‘zing’

As you can see the word “market” and “amazing” was sliced
How do I split the text to 50's but if that splits a word it will be copied to the next filed like this

‘XL500 KOJI – This item is the best item in the’
‘market you can’t find anything else like it 500HP’
‘amazing’

Wim · #2 (**permalink**) 12-20-11, 18:01

Try this:

Code:

DROP TABLE #DaTable
CREATE TABLE #DaTable(
	Id	Int	Identity(1, 1)	NOT NULL,
	LongString	VARCHAR(MAX)	NOT NULL
	CONSTRAINT pk_DaTable PRIMARY KEY(Id)
)
	
INSERT INTO #DaTable(LongString) VALUES
('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'),
('         1         2         3         4         5         6         7         8         9         100'),
('XL500 KOJI – This item is the best item in the market you can’t find anything else like it 500HP amazing')


WITH CTE AS
(SELECT id, 
	1 as seq, 
	SUBSTRING(Longstring, 1, 50 - PATINDEX('% %', REVERSE(SUBSTRING(LongString, 1, 50)))) as Parsed,
	LTRIM(SUBSTRING(Longstring, 50 - PATINDEX('% %', REVERSE(SUBSTRING(LongString, 1, 50))) + 1, 10000)) as Unparsed
FROM #DaTable
	UNION ALL
SELECT id, 
	SEQ + 1 as seq, 
	SUBSTRING(Unparsed, 1, 50 - PATINDEX('% %', REVERSE(SUBSTRING(Unparsed, 1, 50)))) as Parsed,
	LTRIM(SUBSTRING(Unparsed, 50 - PATINDEX('% %', REVERSE(SUBSTRING(Unparsed, 1, 50))) + 1, 10000)) as Unparsed
FROM CTE
WHERE LEN(Unparsed) > 0
)
SELECT Parsed
FROM CTE
ORDER BY Id, seq

Omar.Dweik · #3 (**permalink**) 12-21-11, 09:08

Thank you for the quick response
I tweaked your code a little to this

--Decalre The Full Text as 150 Charcters
Declare @FullName as char(150)
--Decalre The the 3 Sub Strings as 50 Charcters
Declare @SubStr1 as char(50)
Declare @SubStr2 as char(50)
Declare @SubStr3 as char(50)
--Declare the Length of each String
Declare @SubstrLen1 as integer
Declare @SubstrLen2 as integer

set @FullName = ('XL500 KOJI – This item is the best item in the market you can’t find anything else like it 500HP amazing')

--Set the Value of the First String
set @SubStr1 = ltrim(rtrim(SUBSTRING(@FullName, 1, 50 - PATINDEX('% %', REVERSE(SUBSTRING(@FullName, 1, 50))))))
--Claclate the Length of SubString1
set @SubstrLen1 = (len(SUBSTRING(@FullName, 1, 50 - PATINDEX('% %', REVERSE(SUBSTRING(@FullName, 1, 50)))))) +1

--Set the Value of the Secound String
set @SubStr2 = ltrim(rtrim(SUBSTRING(@FullName, @SubstrLen1, 50 - PATINDEX('% %', REVERSE(SUBSTRING(@FullName, @SubstrLen1, 50))))))
--Claclate the Length of SubString2
set @SubstrLen2 = (len(SUBSTRING(@FullName, @SubstrLen1, 50 - PATINDEX('% %', REVERSE(SUBSTRING(@FullName, @SubstrLen1, 50))))) ) +1

--Set the Value of the Third String
set @SubStr3 = ltrim(rtrim(SUBSTRING(@FullName, @SubstrLen1 + @SubstrLen2, 50 - PATINDEX('% %', REVERSE(SUBSTRING(@FullName, @SubstrLen1 + @SubstrLen2, 50))))))

select @FullName as [Full String]
select @SubStr1 as String1
select @SubStr2 as String2
select @SubStr3 as String3

Internet Services

Web Development

Digital Marketing

Consumer Tech