December 8, 2014 at 4:40 am
Hi Folks
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word.
I've identified the spaces using that awfully clever tally table technique, but actually have no idea how to do accomplish what I need 🙁 Any ideas?
I've included the tally stuff below, but it's probably a red herring for what I'm trying to achieve.
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = (select a_notes
from dbo.notestuff as notes
where a_id = '1')
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SET @Parameter = ' '+@Parameter +' '
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that command and
-- the next comma into the Elements table
INSERT INTO @Elements
(Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(' ',@Parameter,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ' ' --Notice how we find the comma
SELECT *
FROM @Elements
December 8, 2014 at 6:38 am
a Slight modification to use an inline Tally table and my own String for demos
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'this is a very long string just being used to demonstrate the text splitting and hopefull it will be great than 50 characters long'
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SET @Parameter = ' '+@Parameter +' ';
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that command and
-- the next comma into the Elements table
WITH Cte_Tally
AS
(
SELECT Row_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM sys.all_columns
)
Insert into @Elements
SELECT SUBSTRING(@Parameter,N,1)
FROM Cte_Tally
WHERE N <= LEN(@Parameter);
Select * From @Elements;
/*
Gets the position closest to 50 characters in each
*/
Select (Number / 50) LineSet, Max(Number) Position
from @Elements where Value=' '
group by (Number / 50);
This will return the positions 47 and 99 for my test data, you might have to play around
After that its simply a case of using the data to then split the string into its constituent parts.
Unfortunately this doesn't exactly work I've just realised theres a flaw in the logic, the position after 47 should be 94 not 99 as this works on the assumption of 50 char blocks.
You could possibly do this based on knowing what the last group split point was and then start from there, LAG would lead to a neater solution, but its not available in 2008,
I have to get back to work now, I'll try and pick this up in the afternoon if I have a lull.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 8, 2014 at 7:10 am
WITH cteStart(N1) AS (
SELECT t.N+1 FROM dbo.Tally t
WHERE t.N < LEN(@Parameter)
AND SUBSTRING(@Parameter,t.N,1) = ' '
),
cteLen(N1,L1) AS (
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(' ',@Parameter,s.N1),0)-s.N1,8000)
FROM cteStart s
),
words (N1,Word,Grp) AS (
SELECT l.N1,SUBSTRING(@Parameter, l.N1, l.L1),CEILING(l.N1 / 50.0)
FROM cteLen l
),
groups (Grp) AS (
SELECT DISTINCT Grp
FROM words
)
SELECTg.Grp,
RTRIM(
SELECTw.word + ' '
FROM words w
WHERE w.Grp = g.Grp
ORDER BY w.N1
FOR XML PATH('')
))
FROM groups g
ORDER BY g.Grp
Far away is close at hand in the images of elsewhere.
Anon.
December 18, 2014 at 8:00 am
Just returned to work and will try both of the suggestions - thanks for taking the time to offer solutions guys, it is much appreciated.
December 18, 2014 at 9:44 am
ldanks (12/18/2014)
Just returned to work and will try both of the suggestions - thanks for taking the time to offer solutions guys, it is much appreciated.
No problem I think David's solution is the one to try first as mine has a few flaws, and from what I can see his handles those reasonably well.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 18, 2014 at 6:44 pm
There is also the following possibility using Jeff Moden's community-improved, delimited string splitter and a quirky update.
DECLARE @Parameter VARCHAR(8000) = 'this is a very long string just being used to demonstrate the text splitting and hopefull it will be great than 50 characters long'
,@LineLength INT = 50
,@CurLength INT = 0
,@LineNumber INT = 0;
DECLARE @Paragraphs TABLE
(
ItemNumber INT
,Item VARCHAR(8000)
,LenItem INT
,LineNumber INT
,PRIMARY KEY (ItemNumber)
);
INSERT INTO @Paragraphs
SELECT *, LEN(Item)+1, 0
FROM dbo.DelimitedSplit8K(@Parameter, ' ');
UPDATE @Paragraphs
SET @LineNumber = LineNumber = CASE WHEN @CurLength + LenItem > @LineLength THEN @LineNumber + 1 ELSE @LineNumber END
,@CurLength = LenItem = CASE WHEN @CurLength + LenItem > @LineLength THEN 0 ELSE @CurLength + LenItem END
OPTION(MAXDOP 1);
SELECT Line=
(
SELECT Item + ' '
FROM @Paragraphs b
WHERE a.LineNumber = b.LineNumber
ORDER BY ItemNumber
FOR XML PATH('')
)
FROM @Paragraphs a
GROUP BY LineNumber;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply