August 12, 2011 at 12:46 pm
I have a long char field (101) and i need to print it on a 4 inch long inventory label. The labeling software, ZebraDesignerPro 2, doesn't word wrap. Does anyone know how i can create a view that would take the table and split it at a specific char length plus a space?? i.e. after 30 characters split at next space.
there are 13000 item descriptions in the table so i can't define where to split each row individually.
any thoughts or ideas?
thanks
August 12, 2011 at 2:13 pm
I ran into a similar issue on a QL220 Zebra Printer. Can you provide an example of what you want done?
August 12, 2011 at 2:27 pm
You could use substring to chop the char(101) into 4 char(30) fields in a view.
Code below to create a view named 'SplitInto30Char', splits 'test_field' which is in a table into 4 chunks.
CREATE VIEW [dbo].[SplitInto30Char]
AS
SELECT
SUBSTRING(test_field, 0, 30) AS section1,
SUBSTRING(test_field, 30, 30) AS section2,
SUBSTRING(test_field, 60, 30) AS section3,
SUBSTRING(test_field, 90, 30) AS section4
FROM dbo.testLong
EDIT: This splits in the middle of words though...hmmm..
August 12, 2011 at 2:39 pm
jpomfret7 (8/12/2011)
You could use substring to chop the char(101) into 4 char(30) fields in a view.Code below to create a view named 'SplitInto30Char', splits 'test_field' which is in a table into 4 chunks.
CREATE VIEW [dbo].[SplitInto30Char]
AS
SELECT
SUBSTRING(test_field, 0, 30) AS section1,
SUBSTRING(test_field, 30, 30) AS section2,
SUBSTRING(test_field, 60, 30) AS section3,
SUBSTRING(test_field, 90, 30) AS section4
FROM dbo.testLong
EDIT: This splits in the middle of words though...hmmm..
yes which is what i'm trying to avoid. the other option i have seen is splitting at the spaces but since the fields would vary greatly in size they would be hard to actually put on the label.
August 12, 2011 at 2:43 pm
This isn't quite 100% but it is very close...just a little tweaking on the substring math and you should be there.
create table #Test
(
Label varchar(101)
)
insert #Test
select 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labores'
insert #Test
select 'this string is exactly 101 character, the longest one you can have according to the data initial size'
insert #Test
select 'thisstringisshortandnospaces'
select Label, LEFT(Label, 30 + CHARINDEX(' ', SUBSTRING(Label, 30, DATALENGTH(Label))) - 2) as Segment1,
SUBSTRING(Label, 29 + CHARINDEX(' ', SUBSTRING(Label, 29, DATALENGTH(Label))), 29 + CHARINDEX(' ', SUBSTRING(Label, 29, DATALENGTH(Label)))) as Segment2,
SUBSTRING(Label, 29 + CHARINDEX(' ', SUBSTRING(Label, 29, DATALENGTH(Label))) + 29 + CHARINDEX(' ', SUBSTRING(Label, 29, DATALENGTH(Label))) , datalength(Label)) as Segment3
from #Test
drop table #Test
_______________________________________________________________
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 12, 2011 at 2:44 pm
Neal Sivley (8/12/2011)
I ran into a similar issue on a QL220 Zebra Printer. Can you provide an example of what you want done?
an example of what im looking for would be this:
ITEMDESC field is 5-1/4" HRPI Anti-Friction Washer Mueller Centurion
the new table be:
itemdesc1: 5-1/4" HRPI Anti-Friction
itemdesc2: Washer Mueller Centurion
which splits the first space after 25 characters.
August 12, 2011 at 2:53 pm
Have you tried to insert a char(10) + char(13) in the middle of the string. The printer should detect the line break.
August 12, 2011 at 3:16 pm
(And the code that splits the string into individual words I have posted is code written by Jeff Moden) that was edited by myself to use your sample data. Putting it back together into a sentence is my pitiful code.
SET NOCOUNT ON
DECLARE @String VARCHAR(MAX),
@Size TINYINT
/*
what if you need to split the long string into a human-readable substrings?
Where every substring length is not longer than a fixed number and split at space characters?
*/
SELECT @String = 'ITEMDESC field is 5-1/4" HRPI Anti-Friction Washer Mueller Centurion'
,@Size = 20
;WITH Peso (Part, StartPos, Size)
AS (
SELECT 1,
1,
CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, 1, @Size) + ' ',
@Size))) AS INT)
UNION ALL
SELECT Part + 1,
StartPos + Size + 1,
CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, StartPos + Size + 1, @Size) + ' ', @Size))) AS INT)
FROM Peso
WHERE StartPos + Size <= DATALENGTH(@String)
)
SELECT Part,StartPos,Size
INTO #T FROM Peso
--Putting it back together as a sentence
DECLARE @sp-2 INT
DECLARE @s-2 INT
DECLARE @Part INT
SET @Part = 1
WHILE @Part < (SELECT MAX(Part)+1 FROM #T)
BEGIN
SELECT @sp-2 = (SELECT StartPos FROM #T WHERE Part = @Part)
SELECT @s-2 = (SELECT Size FROM #T WHERE Part = @Part )
SELECT SUBSTRING(@String,@Sp,@S)
SET @Part = @Part + 1
END
DROP TABLE #T
Result: (20 characters per row ,@Size = 20)
ITEMDESC field is
5-1/4" HRPI
Anti-Friction
Washer Mueller
Centurion
(25 Characters per row ,@Size = 25)
ITEMDESC field is 5-1/4"
HRPI Anti-Friction
Washer Mueller Centurion
This is not going to be quick, in fact most likely too slow for any significant number of rows.
Just hope this little bit helps to get you where you need to be.
August 15, 2011 at 6:30 am
thanks for all the help. I'm going to test this out today and see what i can come up with. i'll let you know if i run into any problems.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply