Save text to binary second line

Question

question

technette asked Jun 3, '14

Save text to binary second line

Hi! I have text that is saved as binary, I use the following to select to read the text: CAST(CAST(PART_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000)) How do I update this binary field to save a line of text on the second line?

binary text

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2014-06-04T11:09:13Z

Kev Riley answered Jun 4, '14

Without asking why you are storing text as binary, here's an example that shows how you can do this --drop table #PART_BINARY create table #PART_BINARY (BITS varbinary(max)); insert into #PART_BINARY select cast('this is sometext' as varbinary(max)) select CAST(CAST(#PART_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000)) from #PART_BINARY update #PART_BINARY set BITS = cast( CAST(CAST(#PART_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000)) + char(13)+ char(10) + 'More text on another line' as varbinary(max)) select CAST(CAST(#PART_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000)) from #PART_BINARY

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette commented · Jun 04, 2014 at 06:17 PM

Thank you Kev! I'm getting the following error: Msg 213, Level 16, State 1, Line 5 Insert Error: Column name or number of supplied values does not match table definition. Here: insert into #PART_BINARY select cast('this is sometext.' as varbinary(max))

0 ·

Kev Riley ♦♦ commented · Jun 05, 2014 at 07:07 AM

you maybe have a cached version of the temp table - run the drop statement for dropping the temp table

0 ·

technette commented · Jun 05, 2014 at 08:45 PM

Thank you Kev! The text is stored as binary as part of an ERP system. This is how it was purchased and implemented years before I came.

0 ·

question