Skip to Content

Convert varchar column to decimal

Hi All,

i have a source column test Varchar(10) in DB2 am trying load this column into destination DB2 ,test decimal 7,0).

Source data look like "ABC00026"

i want to extract last 5 digit from source,so o/p column will be look like 00026

To convert from varchar(10) to decimal (7,0) tried below possibilities.

1 query transform am trying to get 5 digits from source :sub str('ABC00026' ,4,8)

2 nd query transform i got 5 digit from here am trying to convert into decimal so converted by using cast function cast(col,decimal(7,0),to_decimal,valid_decimal.No luck i got only two digits i:e: 26 or nulls.

Its not taking zero's,so to add Zeros used lpad ,length,concatenation function.but No luck.

Waiting for the suggestions.

Thanks in Advance.



Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Jul 17, 2017 at 10:40 AM

    Hi Priyanka Choudam,

    Database will take 00026 as 26 only, even if you add 0 using lpad it will take as 26 only. bcoz 026 equals 26 when it is interger or decimal and 00026 equals 26. So you have to change your target column data type to varchar only to have 00026 in the target.

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 19, 2017 at 10:17 AM

    Hi Priyanka Choudam,

    Nothing to do with DS here as database stores 026 as 26 if column is integer or decimal.


    Ravi kiran

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Priyanka,

      to_char( to_date(sourcefiled ,'YYYYMMDD'),'YYYY/MM/DD') is trying to convert to 'YYYY/MM/DD' and you are trying to load this value to Decimal type target column. Decimal will not hold '/', it will only stores numerics.

      as your source date is in the format YYYYMMDD, you dont need to convert it to decimal as DS will automaticaly converts it to decimal when you map this varchar source column to Decimal target column.

      if you still want to convert it to decimal use CAST function or TO_DECIMAL function.

      Let me know if you still have doubts.


      Ravi kiran

  • Jul 17, 2017 at 11:23 AM

    Hi Ravi,

    Thanks For you reply.

    Yaa even after so many tries i got the same ans.

    do we have right function in DS? any idea.

    by using custom function i need to try as a next step.Pls provide me if you have any ex for creating custom function.

    Add comment
    10|10000 characters needed characters exceeded