Integer to Char conversion questions
Moderators: chulett, rschirm, roy
Integer to Char conversion questions
Hi,
Please need your help in converting Integer to Char function.
I have the below values as Integer in the source.
123456
654321
I have done a direct map across Integer to Char in Transformer.
I am getting the below values
123456??
654321??
In the target table (Oracle) its defined as Char(8)
As searched the forum, it says no need of any conversation from Integer to string.
Any thoughts.
Thanks,
Please need your help in converting Integer to Char function.
I have the below values as Integer in the source.
123456
654321
I have done a direct map across Integer to Char in Transformer.
I am getting the below values
123456??
654321??
In the target table (Oracle) its defined as Char(8)
As searched the forum, it says no need of any conversation from Integer to string.
Any thoughts.
Thanks,
Could it be that you have converted to a CHAR(8)? Did you do an explicit or an implicit conversion in your transform stage?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 62
- Joined: Thu Feb 08, 2007 6:01 am
- Location: Pune
-
- Participant
- Posts: 62
- Joined: Thu Feb 08, 2007 6:01 am
- Location: Pune
-
- Participant
- Posts: 251
- Joined: Mon Jun 09, 2008 5:52 am
Yes - the problem is the CHAR setting. Remember - CHAR fields are fixed length and the one you are converting to is bigger than your integer numbers, so it is padding. It is padding on the right, not the left (usually bad for a number) and looks like it is using a default $APT_STRING_PADCHAR of 0x00 (worse). Note that the padding might have occurred in a previous job if it did a poor job of handling the fixed length CHAR fields. Check the data out in a hex editor to see what the ?? characters really are.
The default of 0x00 (Hex 0) is really a nasty semi-invisible character. The recommendation was to change it to 0x20, which is a space. However, that won't fix the fact it is probably padding on the wrong side of the number.
One solution is to covert them to a VARCHAR stage variable (which will not pad the string). Then pad the VARCHAR on the left with spaces or zeroes (your preference) and move it to the CHAR field. Best practice says that unless the lengths are guaranteed to match exactly, you must ALWAYS pad anything being moved to a CHAR field.
For example:
inputlink.Integer = svVarChar
Right(("00000000":svVarChar),8) = svChar
This would have your numbers come out like this:
00123456
00654321
00000001
Instead of calculating the exact length of the field and add the exact number of zeroes, it is faster to prepend enough to cover the worst case (empty field) and then substring the eight you need to fill the CHAR field.
The default of 0x00 (Hex 0) is really a nasty semi-invisible character. The recommendation was to change it to 0x20, which is a space. However, that won't fix the fact it is probably padding on the wrong side of the number.
One solution is to covert them to a VARCHAR stage variable (which will not pad the string). Then pad the VARCHAR on the left with spaces or zeroes (your preference) and move it to the CHAR field. Best practice says that unless the lengths are guaranteed to match exactly, you must ALWAYS pad anything being moved to a CHAR field.
For example:
inputlink.Integer = svVarChar
Right(("00000000":svVarChar),8) = svChar
This would have your numbers come out like this:
00123456
00654321
00000001
Instead of calculating the exact length of the field and add the exact number of zeroes, it is faster to prepend enough to cover the worst case (empty field) and then substring the eight you need to fill the CHAR field.
Last edited by asorrell on Mon Nov 25, 2013 5:47 pm, edited 2 times in total.
It's a CHAR, they pad on the right as any other string would. Nothing "bad" about that. Now, if you need them left-padded / zero filled, that's a whole different requirement as you've addressed.
[Edit] So noted, modified to say "bad for a number", which it usually is - Andy
[Edit] So noted, modified to say "bad for a number", which it usually is - Andy
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks for the time and replies.
@srinivas.g set is AL32UTF8.
@Andy,
Hex value for ? is 3F
inputlink.Integer = svVarChar ->Varchar(8)
Right(("00000000":svVarChar),8) = svChar -> Char(8)
Iam getting the below values.
00123456
00654321
00000001
But again when I want to remove the leading "0" it with function Trim(svChar,"0","L") getting the below values as output.
123456??
654321??
000001??
I have tried the logic If svChar[1,2] ="00" Then svChar[3,8] Else svChar
getting the below values.
123456??
654321??
000001??
But when I checked the value of svChar[3,8] in peak stage, its coming as 123456
But svChar[3,8] when mapped directly to target table value is coming as 123456??
Any thoughts.
Thank You,
@srinivas.g set is AL32UTF8.
@Andy,
Hex value for ? is 3F
inputlink.Integer = svVarChar ->Varchar(8)
Right(("00000000":svVarChar),8) = svChar -> Char(8)
Iam getting the below values.
00123456
00654321
00000001
But again when I want to remove the leading "0" it with function Trim(svChar,"0","L") getting the below values as output.
123456??
654321??
000001??
I have tried the logic If svChar[1,2] ="00" Then svChar[3,8] Else svChar
getting the below values.
123456??
654321??
000001??
But when I checked the value of svChar[3,8] in peak stage, its coming as 123456
But svChar[3,8] when mapped directly to target table value is coming as 123456??
Any thoughts.
Thank You,
You problem is that your target field is 8 characters long, no more and no less; each position needs to be given a value. So if your value is 6 characters long, what do you expect the remaining 2 to be?
The solution with using a VarChar() and leading zeroes is a viable one. Normally when dealing with numbers your only choice is to use leading "0" or " " (zeroes or spaces).
The TRIM() Function will remove those characters, but since the result needs to be 8 characters long, it will fill these two position with what you see as "?". Basically, you cannot TRIM() a CHAR(n) column, so DataStage will implicitly conver your CHAR() to a VarChar(), perform the trim and then convert the VarChar() back to a CHAR().
The solution with using a VarChar() and leading zeroes is a viable one. Normally when dealing with numbers your only choice is to use leading "0" or " " (zeroes or spaces).
The TRIM() Function will remove those characters, but since the result needs to be 8 characters long, it will fill these two position with what you see as "?". Basically, you cannot TRIM() a CHAR(n) column, so DataStage will implicitly conver your CHAR() to a VarChar(), perform the trim and then convert the VarChar() back to a CHAR().
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
That is by far the best solution.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>