Convert HEX 92 Issue

[lisacancio]

I read through a variety of posts that discuss converting ASCII characters to a HEX value in DataStage and have not been able to find my specific issue. I am extracting data (ODBC connector) which is coming from a Siebel database where there is a character that appears to look like an apostrophy, however it isn't and it's causing issues. I'm trying to identify what the HEX value of this character is so I can replace it with a space. When I put this character through an ASCII to HEX converter calculator it returns as 2019. When I put it in UltraEdit and do a hex dump it indicates it's hex value is 92. I have tried putting these values in my convert function in my Transformer Stage to identify this character and replace it with a space, but it doesn't recognize this character as being a hex 92 or a hex 2019. Any advice would be greatly appreciated.
Thank you,

[chulett]

Post the Convert() syntax you've been trying.

[lisacancio]

if IsNull(DSLink5.DESCRIPTION) then Space(1) else Convert(Char(92),' ',Convert(Char(41),' ', Convert(Char(10),' ',Convert(Char(11),' ',Convert(Char(13),' ',DSLink5.DESCRIPTION)))))

[chulett]

You can't inline them like that. Try something more like:

Code: Select all

else Convert(Char(92):Char(41):Char(10):Char(11):Char(13),'     ',DSLink5.DESCRIPTION)

Note the five spaces in the 'convert to' list.

[BradMiller]

Thank you Craig, I tried the below but it still isn't identifying that character and replacing it with the space:
if IsNull(DSLink5.DESCRIPTION) then Space(1) else Convert(Char(92):Char(2019):Char(10):Char(11):Char(13),' ',DSLink5.DESCRIPTION)

[chulett]

You may not have properly identified the character - and the CHAR() function needs the decimal value, not the hex so that's probably the issue now that I think about it.

[ray.wurlod]

Char() is only good up to 255. For values above this you need UniChar().

[chulett]

But would there really be a character with a value of 2019? Base anything?

Hmmm... I guess so, from googling around a hex 2019 (dec 8217) is the Unicode character name: RIGHT SINGLE QUOTATION MARK.

[lisacancio]

I tried all of the below Decimal values which resemble the apostrophe symbol I'm seeing and my transformer is replacing the value with a small square box into the extracted txt file:
if IsNull(DSLink5.DESCRIPTION) then Space(1) else Convert(Char(146):Char(145):Char(130):Char(31):Char(44):Char(96):Char(10):Char(11):Char(13),' ',DSLink5.DESCRIPTION)

[lisacancio]

I tried to identify the Decimal 8217 and DataStage is having a problem with what I'm entering:
if IsNull(DSLink5.DESCRIPTION) then Space(1) else Convert(UniChar(8217):Char(10):Char(11):Char(13),' ',DSLink5.DESCRIPTION)

[lisacancio]

I also added a peek stage and when I view the data in Director it displays that character as: ?

[ArndW]

You can use the SEQ() or UNISEQ() function to get the numeric equivalent of a single character, that might help you debug your problem.

[lisacancio]

We've determined (via SQL doing a CONVERT(VARBINARY() as well as UltraEdit doing a HEX dump, and via online ASCII/HEX/Decimal calculator converters) the Decimal value of the character is 146, the HEX value is 92. For some odd reason, DataStage doesn't recognize the Decimal value 146. It does recognize Decimal values such as 10, 11, 13.....carriage returns, line feeds, tabs.......I would think someone would have stumbled across this problem already??

[chulett]

More of a workaround in my mind since we didn't find a DataStage solution but glad you got it worked out, regardless.