August 7, 2015 at 12:03 pm
just for reference, this is how i tried to solve it;
i parsed everything with DelimitedSplit8K, and then just fiddled with columns that were larger than the target length.
my sample data is pretty long, i grabbed a couple of recipes as sample data.
note i made sure i had a single word(url) that was longer than 50 characters so you could see that if it hits the limit and is longer than the limit , it will be split no matter what.
DECLARE @MyExample TABLE (ID int identity(1,1),MyRecipe varchar(max),OtherColumns varchar(30) )
INSERT INTO @MyExample
SELECT '***DUST MASK MUST BE WORN***
ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON ROUTE CARD IS CORRECT AGAINST THE PICK SHEET & LABEL
PROCESS STEPS
1.ADD TO THE STEEL BIN
910373 PUMPKIN SEEDS
910375 LINSEED
910731 SUNFLOWER SEEDS
910086 JUMBO OATS
910073 COARSE BRAN
911183 JS NEW MULTISEED IMPROVER
910109 WHOLEMEAL RYE FLOUR
910074 VITAL WHEAT GLUTEN
910362 SALT
260200 FERMDOR
910333 CASTER SUGAR
910118 BISCUIT FLOUR
2. ADD LAST TO BIN 910371 BURNT SUGAR','Some Comments'
UNION ALL
SELECT 'Directions
Combine olive oil, salt, and 1/2 teaspoon black pepper in a small bowl. Center half of the mushrooms and onion on a sheet (12x18 inches) of Reynolds Wrap® Heavy Duty Aluminum Foil. Drizzle with half of the olive oil mixture. Bring up foil sides. Double-fold top and ends to seal packet, leaving room for heat circulation inside. Repeat to make two packets.
Line a baking sheet with Reynolds Wrap® Aluminum Foil. Mix the beef, Worcestershire sauce, and 1/2 teaspoon black pepper in a large bowl until combined. Shape beef mixture into eight thin, 4-inch diameter patties on the prepared baking sheet. Place 1 tablespoon of the blue cheese onto the center of each of four of the patties. Top with the remaining four patties; pinch edges to seal.
Transfer burgers on the foil and foil packets to the rack of an uncovered grill directly over medium-high heat and grill for 10 minutes or until burgers are done (160 degrees F) and vegetables are tender. Toast cut sides of hamburger buns during the last minute of grilling.
Open packets carefully by cutting along top fold with a sharp knife, allowing steam to escape; then open top of foil packet.
Serve burgers on buns with grilled vegetables and lettuce and/or tomato slices, if desired.','Second recipe'
UNION ALL
SELECT 'Ingredients
Queso Sauce:
1 tablespoon unsalted butter
1 tablespoon all-purpose flour
1 cup whole milk
12 ounces Chihuahua or Monterey jack cheese, coarsely grated
1/4 cup grated Parmesan
Kosher salt and freshly ground black pepper
Green Chile Relish:
1 medium poblano chile, roasted, peeled, seeded and thinly sliced
2 Hatch chiles, roasted, peeled, seeded and thinly sliced
1 serrano chile, roasted, peeled, seeded and thinly sliced
1/4 cup red wine vinegar
1 tablespoon honey
2 tablespoons extra-virgin olive oil
3 tablespoons chopped fresh cilantro leaves
Kosher salt and freshly ground black pepper
Pickled Red Onions:
1 1/2 cups red wine vinegar
1/4 cup water
2 tablespoons sugar
1 tablespoon kosher salt
1 medium red onion, peeled, halved and thinly sliced
Burgers:
1 tablespoon canola oil
1 1/2 pounds ground beef
Kosher salt and freshly ground black pepper
4 hamburger buns, split and lightly toasted
12 blue or yellow corn tortilla chips, coarsely crushed
Directions
Watch how to make this recipe
For the queso sauce:
Melt the butter in a small saucepan over medium heat. Whisk in the flour and cook for 1 minute. Add the milk, increase the heat to high and cook, whisking constantly, until slightly thickened, about 5 minutes. Remove from the heat and whisk in the cheese until melted; add the Parmesan and season with salt and pepper, to taste. Keep warm.
For the relish:
Combine all ingredients in a bowl and season with salt and pepper, to taste.
For the pickled red onions:
Bring vinegar, water, sugar and salt to a boil in a small saucepan over medium heat. Remove from the heat and let cool for 10 minutes. Put the onions in a medium bowl, pour the vinegar over, cover and refrigerate for at least 4 hours and up to 48 hours before serving.
For the burgers:
Heat a griddle or large saute pan over high heat. Add the oil and let it heat until it begins to shimmer.
Shape the ground beef with your hands into 4 round patties about 1 1/2 inches thick and season each burger on both sides with salt and pepper, to taste. Cook the burgers until golden brown on both sides and cooked to medium, about 8 minutes.
Put the burgers on the buns and top each with a few tablespoons of the queso sauce, relish, onions and chips.
Recipe courtesy of Bobby Flay
Read more at: http://www.foodnetwork.com/recipes/bobby-flay/throwdowns-green-chile-cheeseburgers-recipe.html?oc=linkback','last recipe'
--SELECT * FROM @MyExample
--CROSS APPLY master.dbo.DelimitedSplit8K(MyRecipe,CHAR(10))
--http://www.sqlservercentral.com/Forums/Topic677895-338-1.aspx
DECLARE @MaxLength int = 50;
with
PreRenderedData
AS(
SELECT * FROM @MyExample
CROSS APPLY master.dbo.DelimitedSplit8K(MyRecipe,CHAR(10))
),
Data as (
select ID,ItemNumber,Item,
MyRecipe AS TheString,
OtherColumns AS TheObjectName,
@MaxLength as TheMaxLength
FROM PreRenderedData WHERE LEN(Item) > @MaxLength
),
BreakDown as (
select 0 as LineNumber,
ItemNumber,
TheObjectName,
TheMaxLength,
cast('' as varchar(max)) as Line,
TheString as Remainder
from Data
union all
select LineNumber + 1 as LineNumber,
ItemNumber,
TheObjectName,
TheMaxLength,
substring(Remainder, 1, TheMaxLength - charindex(' ', reverse(left(Remainder, TheMaxLength)))) as Line,
substring(Remainder, TheMaxLength - charindex(' ', reverse(left(Remainder, TheMaxLength))) + 2, len(Remainder)) as Remainder
from BreakDown
where Remainder <> ''
)
select OtherColumns AS TheObjectName,
ItemNumber,
0 AS LineNumber,
Item FROM PreRenderedData WHERE LEN(Item) <= @MaxLength
UNION
select TheObjectName,
ItemNumber,
LineNumber,
Line
from BreakDown
where LineNumber > 0 ORDER BY TheObjectName,ItemNumber,LineNumber;
Lowell
August 7, 2015 at 12:08 pm
Thanks for the heads-up Luis Cazares. See updated script:
DECLARE @MaxLength int = 50,
@String varchar(max)
SELECT @String = 'CLEAN PREVIOUS PRODUCT: FOR DETAILS OF NAME, PRODUCT CODE, BATCH DETAIL AND ALLERGENS SEE CLEANING SHEET'
;WITH
BreakDown as (
SELECT 0 as LineNumber,
@String As TheOriginalString,
CAST('' AS VARCHAR(MAX)) as Line,
@String As Remainder
UNION ALL
SELECT LineNumber + 1 as LineNumber,
TheOriginalString,
SUBSTRING(Remainder, 1, CASEWHEN NULLIF(CHARINDEX(CHAR(10), Remainder), 0) < @MaxLength - charindex(' ', reverse(left(Remainder, @MaxLength)))
THEN ISNULL( NULLIF(CHARINDEX(CHAR(10), Remainder), 0), @MaxLength)
ELSE@MaxLength - charindex(' ', reverse(left(Remainder, @MaxLength))) END) as Line,
SUBSTRING(Remainder, CASEWHEN NULLIF(CHARINDEX(CHAR(10), Remainder), 0) < @MaxLength - charindex(' ', reverse(left(Remainder, @MaxLength)))
THEN CHARINDEX(CHAR(10), Remainder) + 1 ELSE @MaxLength - charindex(' ', reverse(left(Remainder, @MaxLength))) + 2 END, len(Remainder)) as Remainder
FROM BreakDown
WHERE Remainder <> ''
)
select --TheOriginalString,
LineNumber,
Line
from BreakDown
where LineNumber > 0;
August 7, 2015 at 12:10 pm
Thank you Lowell. Will have a go at it.
August 7, 2015 at 12:36 pm
Lowell (8/7/2015)
just for reference, this is how i tried to solve it;i parsed everything with DelimitedSplit8K, and then just fiddled with columns that were larger than the target length.
my sample data is pretty long, i grabbed a couple of recipes as sample data.
note i made sure i had a single word(url) that was longer than 50 characters so you could see that if it hits the limit and is longer than the limit , it will be split no matter what.
I was fiddling with a version of my own using that same function when Luis posted his. I scrapped mine immediately as I saw the approach he was taking was much better. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 12:38 pm
Sean Lange (8/7/2015)
I was fiddling with a version of my own using that same function when Luis posted his. I scrapped mine immediately as I saw the approach he was taking was much better. 😎
agreed, i just thought having some not-as-good code for contrast would be nice. i went to the trouble of making it work, might as well buff my post count up with it.
Lowell
August 7, 2015 at 12:51 pm
Lowell (8/7/2015)
Sean Lange (8/7/2015)
I was fiddling with a version of my own using that same function when Luis posted his. I scrapped mine immediately as I saw the approach he was taking was much better. 😎agreed, i just thought having some not-as-good code for contrast would be nice. i went to the trouble of making it work, might as well buff my post count up with it.
heh mine would certainly have been subpar as it wasn't refined enough to return the correct results. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 1:37 pm
Hi Luis Cazares,
I changed the @MaxLength to 30 and it broke. Can't see anywhere in there with 50, not sure why it broke.
'CLEAN PREVIOUS PRODUCT: FOR DETAILS OF NAME, PRODUCT CODE, BATCH DETAIL AND ALLERGENS SEE CLEANING SHEET'
Result:
CLEAN PREVIOUS PRODUCT: FOR
DETAILS OF NAME, PRODUCT
CODE, BATCH DETAIL AND
ALLERGENS SEE CLEANING S
EET
Thank you.
August 10, 2015 at 7:15 am
After a good weekend of rest and a morning coffee, this is what I came out with. It's reducing the conditions by splitting the lines into separate rows before the max char validation. This seems to be very similar to the one Lowell posted before. I didn't think on checking it carefully before.
DECLARE @String varchar(8000) = '***DUST MASK MUST BE WORN***
ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON ROUTE CARD IS CORRECT AGAINST THE PICK SHEET & LABEL
PROCESS STEPS
1.ADD TO THE STEEL BIN
910373 PUMPKIN SEEDS
910375 LINSEED
910731 SUNFLOWER SEEDS
910086 JUMBO OATS
910073 COARSE BRAN
911183 JS NEW MULTISEED IMPROVER
910109 WHOLEMEAL RYE FLOUR
910074 VITAL WHEAT GLUTEN
910362 SALT
260200 FERMDOR
910333 CASTER SUGAR
910118 BISCUIT FLOUR
2. ADD LAST TO BIN 910371 BURNT SUGAR',
@MaxLength int = 30;
--SET @String = 'CLEAN PREVIOUS PRODUCT: FOR DETAILS OF NAME, PRODUCT CODE, BATCH DETAIL AND ALLERGENS SEE CLEANING SHEET';
with
BreakDown as (
select 0 as LineNumber,
@String As TheObjectName,
cast('' as varchar(8000)) as Line,
Item as Remainder,
ItemNumber
FROM dbo.DelimitedSplit8K( @String, CHAR(13))
union all
select LineNumber + 1 as LineNumber,
TheObjectName,
CASE WHEN LEN( Remainder) > @MaxLength --Only split when necessary
THEN substring(Remainder, 1, @MaxLength - charindex(' ', reverse(left(Remainder, @MaxLength))))
ELSE Remainder END as Line,
CASE WHEN LEN( Remainder) > @MaxLength --Only split when necessary
THEN substring(Remainder, @MaxLength - charindex(' ', reverse(left(Remainder, @MaxLength))) + 2, len(Remainder))
ELSE '' END as Remainder,
ItemNumber
from BreakDown
where Remainder <> ''
)
select ROW_NUMBER() OVER(ORDER BY ItemNumber, LineNumber) AS LineNumber,
REPLACE( Line, CHAR(10), '') Line
from BreakDown
where LineNumber > 0;
August 10, 2015 at 7:49 am
Great! works perfectly.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply