Convert HEX 92 Issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 7
- Joined: Tue Jul 13, 2010 1:00 pm
Convert HEX 92 Issue
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,
Thank you,
~Lisa
-
- Participant
- Posts: 7
- Joined: Tue Jul 13, 2010 1:00 pm
Convert HEX 92 Issue
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)))))
~Lisa
You can't inline them like that. Try something more like:
Note the five spaces in the 'convert to' list.
Code: Select all
else Convert(Char(92):Char(41):Char(10):Char(11):Char(13),' ',DSLink5.DESCRIPTION)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 87
- Joined: Mon Feb 18, 2008 3:58 pm
- Location: Sacramento, CA
Convert HEX 92 Issue
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)
if IsNull(DSLink5.DESCRIPTION) then Space(1) else Convert(Char(92):Char(2019):Char(10):Char(11):Char(13),' ',DSLink5.DESCRIPTION)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 7
- Joined: Tue Jul 13, 2010 1:00 pm
Convert ASCII to Decimal
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)
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)
~Lisa
-
- Participant
- Posts: 7
- Joined: Tue Jul 13, 2010 1:00 pm
-
- Participant
- Posts: 7
- Joined: Tue Jul 13, 2010 1:00 pm
-
- Participant
- Posts: 7
- Joined: Tue Jul 13, 2010 1:00 pm
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??
~Lisa