SQL:Zero leading at insert into Image column

Question

I am trying to insert a HEX representation of XML (I get HEX string of xml file) into a Image column.

I tried this:

if exists (select [Version] from [...].[dbo].[Table] where Version ='1.1.1.1') update [.....].[dbo].[Table] set [Xsd] = 0xEFBBBF3C3F7857465466.... else insert into [....].[dbo].[Table] ([Version], [Xsd]) values ('1.1.1.1',0xEFBBBF3C3F7857465466....) go

But, when I execute this query, I get first zero:

0x0EFBBBF3C3F7857465466.... - Image field, can NULL.

How to fix it? I try to make replace try to make replace:

Select replace(rtrim(replace('0x0EFBBBF3C3F7857465466....','0',' ')),' ','0');

But get decimal-version: 307854...

And length is 217847 (I count from 0).

Another strings inserts without zero!

It seems that it is alignment at SQL Server: when I add 'E' symbol at the end of string- it makes a valid begin of string: 0xEFBB....

EDIT: i remove quotes ,but it does not works.

ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. See details here — marc_s
– marc_s, Commented Jan 14, 2015 at 19:43
Why the INSERT statement have the HEX value between quotes? What is the data type of column Xsd? You are doing implicit castings there. — Ricardo C
– Ricardo C, Commented Jan 14, 2015 at 20:33
SQL Server doesn't have a natural way to represent a set of bits that isn't divisible by 8 - if you ask it to store 12 bits (say, 0xABC) it will round that up and store those bits in two bytes. When you ask for those bits back, you'll find a 0 on the front for the extra four bits it had to store. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 15, 2015 at 8:46

user2545071 · Accepted Answer · 2015-01-15 08:46:48Z

Okey, i fix this problem!

It seems that data corrupte when i copy\paste it from text editor;

So, i have to open my *.xml file from Hex editor (Hex Editor Neo), go-> View->Tool Windows->Copy&Export;

It will make window; At that window - select option - Without separators.-> it will make simple one-line string of file. So, copy all selected string- Press Copy and then Paste it into sql file.

You can use some other HEX-editor.

Collectives™ on Stack Overflow

SQL:Zero leading at insert into Image column

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related