Getting Unknown characters while Fetching from SQL SERVER

Rakesh311 · Post by **Rakesh311** » Fri Oct 07, 2011 8:57 am

Hi

I am fetching data from an CHAR(5) COLUMN of an SQL SERVER 2005 database.If the count of number of characters in that column is less than 5,then am getting some unknown characters for the rest...
Example
Column1
AA^@^@^@
aaaaa
b^@^@^@^@

This Unknown character(^@) is not visible from datastage(while viewing the data)

But when i checked the file at the server i found this...this make the string comparison and all fail...

can u guys please help me in resolving this

Thanks
Rakesh

chulett · Post by **chulett** » Fri Oct 07, 2011 9:04 am

First step is always to move it from "unknown character" to known by determining the decimal or hex value of the character. A hex editor would make that simple or you can check your man page for "od" to see what your switches are to output the character in something besides octal.

Or you could change your value of $APT_STRING_PADCHAR (that should be pretty close to the actual name) from 0x0 to 0x20 so they are padded with spaces.

chulett · Post by **chulett** » Fri Oct 07, 2011 9:07 am

ps. Just post your questions, please don't send it out via private messaging as well. There's no need and it just upsets the natives.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 07, 2011 9:18 am

pps. The second person personal pronoun in English is spelled "you". Spelling it "u" upsets at least some of the natives.

chulett · Post by **chulett** » Fri Oct 07, 2011 9:20 am

LOL... that too.

Rakesh311 · Post by **Rakesh311** » Fri Oct 07, 2011 9:39 am

Mistakes

Sorry guys...

Craig thanks for your reply....

I had tried by finding the Ascii value of those Character..but its not working as expected...

And when I set the $APT_STRING_PADCHAR to 0x20 the columns with at-least one character's value is coming perfectly.
But if there is no Character then its not happening.

Example
Column1
A
^@^@^@^@^@

Thanks
Rakesh

ray.wurlod · Post by **ray.wurlod** » Fri Oct 07, 2011 4:57 pm

Ctrl-@ is the ASCII NUL character, which is used to pad fixed length strings by default. Setting APT_STRING_PADCHAR to a different character uses that character instead. For the case where SQL Server returns five NUL characters I'd be interested to know what's really stored in SQL Server. You can certainly convert Char(0) to " " in a Transformer stage, but how did the NUL characters get into SQL Server in the first place?