How to store null character literal in table

Question

How to store null character literal in table

saum70

SSC Eights!

Points: 993
More actions
June 20, 2017 at 1:21 am

#337679

Hi,
One of the requirement is to store the values containing any character in the table A which may include null character literal i.e. '\0'. The value will be provided by application developed in VC++ in which they do memcopy the actual value into a variable. Due to this the original value gets converted into any junk value.
By replacing the null character with other value may change the original value when converting back again.
The records in this table A is bulk inserted.
Kindly guide for the same.
Saumik Vora

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

saum70 - Tuesday, June 20, 2017 1:21 AM
Hi,
One of the requirement is to store the values containing any character in the table A which may include null character literal i.e. '\0'. The value will be provided by application developed in VC++ in which they do memcopy the actual value into a variable. Due to this the original value gets converted into any junk value.
By replacing the null character with other value may change the original value when converting back again.
The records in this table A is bulk inserted.
Kindly guide for the same.
Saumik Vora

Don't think you need any conversion, look at this example
😎

USE TEEST; GO SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_TEST_ZERO_CHAR') IS NOT NULL DROP TABLE dbo.TBL_TEST_ZERO_CHAR;

CREATE TABLE dbo.TBL_TEST_ZERO_CHAR ( TZC_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_ZERO_CHAR_TZC_ID PRIMARY KEY CLUSTERED ,TZC_STR VARCHAR(100) NOT NULL );

INSERT INTO dbo.TBL_TEST_ZERO_CHAR(TZC_STR) VALUES ('ABC' + CHAR(0) + 'DEF') ,(CHAR(0) + 'GHI');

SELECT TC.TZC_ID AS TZC_ID ,TC.TZC_STR AS TZC_STR ,DATALENGTH(TC.TZC_STR) AS TZC_DATALENGTH ,LEN(TC.TZC_STR) AS TZC_LEN ,CONVERT(VARBINARY(100),TC.TZC_STR,0) AS TZC_VARBINARY FROM dbo.TBL_TEST_ZERO_CHAR TC;

Output
TZC_ID TZC_STR TZC_DATALENGTH TZC_LEN TZC_VARBINARY ----------- -------- -------------- ----------- ----------------- 1 ABC DEF 7 7 0x41424300444546 2 GHI 4 4 0x00474849 ='font-size:9.0pt;mso-bidi-font-size:11.0pt;line-height:107%;font-family:"courier>

saum70 SSC Eights! Points: 993 More actions · Answer 2

Only ABC is getting stored in the table in my case. Blank gets stored for second. Though the value in the rest of the columns matches.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

when checking that the data is stored on the db do convert to varbinary to confirm. A x00 value will truncate the output on SSMS so its not a valid test

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

All the data is there, SSMS cannot display anything after the 0 character but that is a totally different thing😉
😎