Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    2

    Unanswered: Split long text without cutting words

    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’

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2011
    Posts
    2

    I found It

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •