Removing Leading Space and Trailing Dot from Numeric Fields

Jayanto · Post by **Jayanto** » Mon Mar 04, 2013 8:53 am

Hello All,
I need to remove a leading space and trailing '.' from my source data.
The source is a mainframe file. The source field attributes are --
Native Type : DISPLAY NUMERIC
Length : 3
Scale : 0
Level Number : 3
USAGE : DISPLAY
SQL Type : Decimal
Storage Length : 3
Picture : PIC 93

Now lets say my Input is coming as ' 999.' -->> My desired output is '999'.
The Target Datatype is - Numeric(3).

Please advice on how to move ahead with replacing/removing it.. Tried using the Convert & Trim Functions.. But nothing positive..!!

chulett · Post by **chulett** » Mon Mar 04, 2013 10:14 am

Output it as a string so you have full control over the formatting.

Jayanto · Post by **Jayanto** » Mon Mar 04, 2013 10:25 am

@Chulett -- Thanks.. But what function to use??

sudha03_vpr · Post by **sudha03_vpr** » Mon Mar 04, 2013 10:52 am

Ereplace(MyString, ".", "")

You can probably use the above function to replace "." with nothing.

Jayanto · Post by **Jayanto** » Mon Mar 04, 2013 11:41 am

@Sudha01_vpr -- I tried Ereplace(), but it was not working.. It works only with strings..!!
Also, as per my findings in DSXchange-EReplace() works with version 9.1, and pxEReplace() is not advised to be used.. Am working with DataStage 8.7..
Let me try it once with DecimalToString(), but again the datatypes needed to be tweaked..!!

sudha03_vpr · Post by **sudha03_vpr** » Mon Mar 04, 2013 2:14 pm

Yes. You can probably use DecimalToString and then get the length of the string and get the substring before . and store it back to Numeric Field.

chulett · Post by **chulett** » Mon Mar 04, 2013 2:23 pm

Jayanto wrote:Thanks.. But what function to use??

DecimalToString. And if you still need to, Convert after that.

sudha03_vpr · Post by **sudha03_vpr** » Mon Mar 04, 2013 2:39 pm

Yes. You can probably use DecimalToString and then get the length of the string and get the substring before . and store it back to Numeric Field.

priyadarshikunal · Post by **priyadarshikunal** » Mon Mar 04, 2013 3:32 pm

Numeric datatype will have the . when printed to a file. File doesn't have any datatype as such, so you can change the datatype in datastage. Reading program can still read it as decimal.

jwiles · Post by **jwiles** » Mon Mar 04, 2013 4:58 pm

Assuming that your data is comprised of whole numbers, you can change it to an Integer datatype before the output file (expanding on Priyad's suggestion). Alternatively, you have the option of specifying a format string (C_format or Out_format options) in the column's metadata in the output sequential file stage.

There was a similar thread to this from just a week or two ago...a little bit of searching might have turned it up.

Regards,

chandra.shekhar@tcs.com · Mon Mar 04, 2013 11:46 pm

@Jayanto,
Use simple TRIM function and store the output as Integer.

srinivas.nettalam · Post by **srinivas.nettalam** » Tue Mar 05, 2013 8:07 am

Convert('.','',DecimalToString(Col))

prasson_ibm · Post by **prasson_ibm** » Tue Mar 05, 2013 9:03 am

This should also work:-

Code: Select all

Left(DecimalToString(Inputcol),Index(DecimalToString(InputCol),".",1)-1)

ray.wurlod · Post by **ray.wurlod** » Tue Mar 05, 2013 1:24 pm

Or, perhaps simpler:

Code: Select all

Field(DecimalToString(Inputcol),".",1)

FranklinE · Post by **FranklinE** » Wed Mar 06, 2013 12:07 pm

In COBOL, display numerics can be treated as a subset of alphanumeric. The key is to make sure you provide "space" for the decimal.

Code: Select all

PIC 9(3) == Char(3)
PIC 9(3)V9(2) == Char(6)
etc.

The sign is usually incorporated in the first (rarely) or last (standard) digit. The EBCDIC character displays instead of the numeric. Ex. unsigned 0 = 0 (x'F0'), positive 0 = } (x'C0'), negative 0 = { (x'D0'). C and D are also the final half-byte in signed packed decimals.