Skip to Content
0

Convert varchar column to decimal

Jul 14, 2017 at 06:17 AM

130

avatar image

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.

Regards,

Priyanka.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Ravi kiran Pagidi Jul 17, 2017 at 10:40 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Ravi kiran Pagidi Jul 19, 2017 at 10:17 AM
1

Hi Priyanka Choudam,

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

Thanks,

Ravi kiran

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thanks Ravi for you are time.

i have one more question.

i need to convert varchar date to decimal .

source is having date type varchar (20) (i:e YYYYMMDD) to decimal(8,0) .

i tried to_char( source field,'YYYYMMDD') its working but if i want to change the format i need to use something like

to_char( to_date(sourcefiled ,'YYYYMMDD'),'YYYY/MM/DD') correct me if am wrong.

after converting am getting Null value.

Please let me know if you have better idea.

Regards,

Priyanka.

0

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.

Thanks,

Ravi kiran

0
Priyanka Choudam Jul 17, 2017 at 11:23 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Better to make the column as varchar(). Decimal column cannot be prefixed with 000

Regards

Arun Sasi

1