August 7, 2015 at 6:22 am
Hello,
I am submitting a form on a webpage to a MSSQL database using a textarea, cols is set to "50", wrap is "hard" and everything looks as it should be on the web.
What I want is a way to move every line to a new row after every 50 characters, include blank line and lines that are less than 50 characters.
See sample data below: (one cell)
***DUST MASK MUST BE WORN***
ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON ROUTE CARD IS CORRECT AGAINST THE PICK
PROCESS
1.ADD TO THE STEEL BIN
2. ADD LAST TO BIN
3. ADD NOTHING
Expected Result: (10 rows)
1. ***DUST MASK MUST BE WORN***
2. ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON
3. ROUTE CARD IS CORRECT AGAINST THE PICK
4.
5. PROCESS
6. 1.ADD TO THE STEEL BIN
7.
8. 2. ADD LAST TO BIN
9.
10. 3. ADD NOTHING
Thank you.
August 7, 2015 at 6:45 am
Why are you trying to change this when you could store the whole text without problems?
It can be done, but it requires some validation and careful code and testing to get the desired results. I'll try to get this done as an exercise, but I strongly recommend you to keep the text as it is.
August 7, 2015 at 7:01 am
We are looking at exporting the data into another system which only allows 50 characters per line, hence the reason for changing it.
The numbering is not required, I added it to show how many lines are required in the final data set.
Thank you for your time.
August 7, 2015 at 7:49 am
Lowell wrote up a script to do this sort of thing in the past. I will ping him and see if he can post script in here for that since I can't seem to find it anywhere.
_______________________________________________________________
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 8:43 am
Sean pinged me on this one, and i've posted versions of it elsewhere, but i have code saved from other members posts, so let me point you to them.
Lowell (11/13/2013)
take a look at this thread, where there's a couple of different ways the same problem was tackled:Large String Value needs to be broken down into Human readible fixed lengths.
the idea here is to find find the end of a word after a certain number of chars, and wrap it to the next line;
this is to avoid hard breaks in the middle of a word, right?
my specific example i have is just grabbing definitions from sys.sql_modules, and wrapping them at 80 characters max a s a proof of concept, but it came from that thread
--http://www.sqlservercentral.com/Forums/Topic677895-338-1.aspx
DECLARE @MaxLength int = 80;
with
Data as (
select TOP 5 convert(varchar(max),definition) As TheString,
object_name(object_id) As TheObjectName,
@MaxLength as TheMaxLength
from sys.sql_modules
),
BreakDown as (
select 0 as LineNumber,
TheObjectName,
TheMaxLength,
cast('' as varchar(max)) as Line,
TheString as Remainder
from Data
union all
select LineNumber + 1 as LineNumber,
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 TheObjectName,
LineNumber,
Line
from BreakDown
where LineNumber > 0;
Lowell
August 7, 2015 at 9:20 am
Hi,
Thank you for this. I ran it but not sure I understand what it does.
I just want to test it with this string of data below at 50 character split, kindly repost on how to achieve with the text below:
"ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON ROUTE CARD IS CORRECT AGAINST THE PICK SHEET & LABEL"
This should give us 3 lines:
ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON
ROUTE CARD IS CORRECT AGAINST THE PICK SHEET &
LABEL
Thank you.
August 7, 2015 at 9:26 am
when modified to your example, that's exactly what it does:
/*--results:
TheObjectNameLineNumberLine
Some Comments1ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON
Some Comments2ROUTE CARD IS CORRECT AGAINST THE PICK SHEET &
Some Comments3LABEL
*/
--http://www.sqlservercentral.com/Forums/Topic677895-338-1.aspx
DECLARE @MaxLength int = 50;
with
Data as (
select convert(varchar(max),'ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON ROUTE CARD IS CORRECT AGAINST THE PICK SHEET & LABEL') As TheString,
'Some Comments' As TheObjectName,
@MaxLength as TheMaxLength
),
BreakDown as (
select 0 as LineNumber,
TheObjectName,
TheMaxLength,
cast('' as varchar(max)) as Line,
TheString as Remainder
from Data
union all
select LineNumber + 1 as LineNumber,
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 TheObjectName,
LineNumber,
Line
from BreakDown
where LineNumber > 0;
Lowell
August 7, 2015 at 9:32 am
Great, perfect!
But when I add more data, it's not the same.
This is how my data is stored in MSSQL
'***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'
And hoping to get this:
1. ***DUST MASK MUST BE WORN***
2. ENSURE THE PRODUCT CODE AND BATCH INFORMATION ON
3. ROUTE CARD IS CORRECT AGAINST THE PICK SHEET &
4. LABEL
5.
6. PROCESS STEPS
7. 1.ADD TO THE STEEL BIN
8. 910373 PUMPKIN SEEDS
9. 910375 LINSEED
10. 910731 SUNFLOWER SEEDS
11. 910086 JUMBO OATS
12. 910073 COARSE BRAN
13. 911183 JS NEW MULTISEED IMPROVER
14. 910109 WHOLEMEAL RYE FLOUR
15. 910074 VITAL WHEAT GLUTEN
16. 910362 SALT
17. 260200 FERMDOR
18. 910333 CASTER SUGAR
19. 910118 BISCUIT FLOUR
20.
21. 2. ADD LAST TO BIN 910371 BURNT SUGAR'
Basically keeping all the carriage returns and line feeds
August 7, 2015 at 10:02 am
I might have overcomplicated the query, I probably need some rest. 😀
DECLARE @MaxLength int = 50,
@String varchar(max) = '***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';
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, CASE WHEN CHARINDEX(CHAR(10), Remainder) < @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, CASE WHEN 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;
The most important thing is that you're able to understand the query before implementing it. If something goes wrong, you're not allowed to blame us and will need to support the code.
August 7, 2015 at 10:07 am
ahh, better than what i was trying next, Luis!
i started splitting the all data on CHAR(10), and then splitting whatever had a length that was over @MaxLength ;
i like yours better.
Lowell
August 7, 2015 at 10:09 am
My goodness, you just saved my life. Thank you sooooooooooo much. Works perfectly.
August 7, 2015 at 10:12 am
Lowell & Luis Cazares, thank you sooooooo much guys.
Much appreciated!
August 7, 2015 at 10:17 am
You're welcome, just remember to fully understand the code.
August 7, 2015 at 10:47 am
Guys, one more thing, it seems like words are being truncated and repeated:
'CLEAN PREVIOUS PRODUCT: FOR DETAILS OF NAME, PRODUCT CODE, BATCH DETAIL AND ALLERGENS SEE CLEANING SHEET'
Try this text, it shows as:
1. CLEAN PREVIOUS PRODUCT: FOR DETAILS OF NAME, PRODU
2. PRODUCT CODE, BATCH DETAIL AND ALLERGENS SEE CLEAN
3. CLEANING SHEET
PRODUCT & CLEANING were truncated as (PRODU & CLEAN) and repeated fully on next line. It should read:
1. CLEAN PREVIOUS PRODUCT: FOR DETAILS OF NAME,
2. PRODUCT CODE, BATCH DETAIL AND ALLERGENS SEE
3. CLEANING SHEET
Thank you.
August 7, 2015 at 11:40 am
I missed a NULLIF function. If you can solve it, then you should be good to go.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply