How to preserve leading zeros when converting to a decimal in oracle

I am running the below query in oracle.

WITH

ta AS (

     SELECT account_coid

        ,txn_id

        ,cbdev.cbzdt(effective_date) AS effective_date

        ,cbdev.cbchr(utl_raw.substr(txn_data, 113, 20)) AS CESG_amt

     FROM bs_transaction

     WHERE sub_type = 127469880)

SELECT

cast(ta.CESG_amt as DECIMAL (20,2)) AS cesg_amt

from ta 

inner join ....

Here, i m getting the result (cesg_amt) as -156.57. But i need the result as -0000000000156.57.

I need the leading zeros with - retained (leading 0's and also the two digits after the decimal).

I have tried as to_char(ta.CESG_amt, '0000000000000.00') AS cesg_amt in the query but of no use.

Can you please help me what needs to be done in the DECIMAL field to get the result as below.

edited Nov 21 '18 at 12:43

Barbaros Özhan

13.3k71633

asked Nov 21 '18 at 12:02

Satyanvesh D

32118

2

to_char() should be doing what you want.

– Gordon Linoff
Nov 21 '18 at 12:05

In what way is to_char() of no use? What happens when you use it? Also, it is most common to use the NUMBER datatype in Oracle, rather than DECIMAL.

– Boneist
Nov 21 '18 at 12:12

@Boneist when i use to_char() in this case, i m getting the value as 0.00 surprisingly. I m using this query in a script with calls to the oracle db. Any idea about what might be wrong.

– Satyanvesh D
Nov 21 '18 at 14:23

in a script with calls to the oracle db what kind of script? A unix script? A SQLPlus script? Something else? What happens if you run the query directly against the database (e.g. in SQLPLus/Toad/SQL Developer/etc)?

– Boneist
Nov 21 '18 at 14:32

add a comment |

I am running the below query in oracle.

WITH

ta AS (

     SELECT account_coid

        ,txn_id

        ,cbdev.cbzdt(effective_date) AS effective_date

        ,cbdev.cbchr(utl_raw.substr(txn_data, 113, 20)) AS CESG_amt

     FROM bs_transaction

     WHERE sub_type = 127469880)

SELECT

cast(ta.CESG_amt as DECIMAL (20,2)) AS cesg_amt

from ta 

inner join ....

I have tried as to_char(ta.CESG_amt, '0000000000000.00') AS cesg_amt in the query but of no use.

Can you please help me what needs to be done in the DECIMAL field to get the result as below.

edited Nov 21 '18 at 12:43

Barbaros Özhan

13.3k71633

asked Nov 21 '18 at 12:02

Satyanvesh D

32118

2

to_char() should be doing what you want.

– Gordon Linoff
Nov 21 '18 at 12:05

In what way is to_char() of no use? What happens when you use it? Also, it is most common to use the NUMBER datatype in Oracle, rather than DECIMAL.

– Boneist
Nov 21 '18 at 12:12

@Boneist when i use to_char() in this case, i m getting the value as 0.00 surprisingly. I m using this query in a script with calls to the oracle db. Any idea about what might be wrong.

– Satyanvesh D
Nov 21 '18 at 14:23

in a script with calls to the oracle db what kind of script? A unix script? A SQLPlus script? Something else? What happens if you run the query directly against the database (e.g. in SQLPLus/Toad/SQL Developer/etc)?

– Boneist
Nov 21 '18 at 14:32

add a comment |

I am running the below query in oracle.

WITH

ta AS (

     SELECT account_coid

        ,txn_id

        ,cbdev.cbzdt(effective_date) AS effective_date

        ,cbdev.cbchr(utl_raw.substr(txn_data, 113, 20)) AS CESG_amt

     FROM bs_transaction

     WHERE sub_type = 127469880)

SELECT

cast(ta.CESG_amt as DECIMAL (20,2)) AS cesg_amt

from ta 

inner join ....

I have tried as to_char(ta.CESG_amt, '0000000000000.00') AS cesg_amt in the query but of no use.

Can you please help me what needs to be done in the DECIMAL field to get the result as below.

edited Nov 21 '18 at 12:43

Barbaros Özhan

13.3k71633

asked Nov 21 '18 at 12:02

Satyanvesh D

32118

I am running the below query in oracle.

WITH

ta AS (

     SELECT account_coid

        ,txn_id

        ,cbdev.cbzdt(effective_date) AS effective_date

        ,cbdev.cbchr(utl_raw.substr(txn_data, 113, 20)) AS CESG_amt

     FROM bs_transaction

     WHERE sub_type = 127469880)

SELECT

cast(ta.CESG_amt as DECIMAL (20,2)) AS cesg_amt

from ta 

inner join ....

I have tried as to_char(ta.CESG_amt, '0000000000000.00') AS cesg_amt in the query but of no use.

Can you please help me what needs to be done in the DECIMAL field to get the result as below.

sql oracle decimal

edited Nov 21 '18 at 12:43

Barbaros Özhan

13.3k71633

asked Nov 21 '18 at 12:02

Satyanvesh D

32118

edited Nov 21 '18 at 12:43

Barbaros Özhan

13.3k71633

asked Nov 21 '18 at 12:02

Satyanvesh D

32118

edited Nov 21 '18 at 12:43

Barbaros Özhan

13.3k71633

edited Nov 21 '18 at 12:43

Barbaros Özhan

13.3k71633

edited Nov 21 '18 at 12:43

Barbaros Özhan

13.3k71633

asked Nov 21 '18 at 12:02

Satyanvesh D

32118

asked Nov 21 '18 at 12:02

Satyanvesh D

32118

asked Nov 21 '18 at 12:02

Satyanvesh D

32118

2

to_char() should be doing what you want.

– Gordon Linoff
Nov 21 '18 at 12:05

In what way is to_char() of no use? What happens when you use it? Also, it is most common to use the NUMBER datatype in Oracle, rather than DECIMAL.

– Boneist
Nov 21 '18 at 12:12

@Boneist when i use to_char() in this case, i m getting the value as 0.00 surprisingly. I m using this query in a script with calls to the oracle db. Any idea about what might be wrong.

– Satyanvesh D
Nov 21 '18 at 14:23

in a script with calls to the oracle db what kind of script? A unix script? A SQLPlus script? Something else? What happens if you run the query directly against the database (e.g. in SQLPLus/Toad/SQL Developer/etc)?

– Boneist
Nov 21 '18 at 14:32

add a comment |

2

to_char() should be doing what you want.

– Gordon Linoff
Nov 21 '18 at 12:05

In what way is to_char() of no use? What happens when you use it? Also, it is most common to use the NUMBER datatype in Oracle, rather than DECIMAL.

– Boneist
Nov 21 '18 at 12:12

@Boneist when i use to_char() in this case, i m getting the value as 0.00 surprisingly. I m using this query in a script with calls to the oracle db. Any idea about what might be wrong.

– Satyanvesh D
Nov 21 '18 at 14:23

in a script with calls to the oracle db what kind of script? A unix script? A SQLPlus script? Something else? What happens if you run the query directly against the database (e.g. in SQLPLus/Toad/SQL Developer/etc)?

– Boneist
Nov 21 '18 at 14:32

to_char() should be doing what you want.

– Gordon Linoff
Nov 21 '18 at 12:05

In what way is to_char() of no use? What happens when you use it? Also, it is most common to use the NUMBER datatype in Oracle, rather than DECIMAL.

– Boneist
Nov 21 '18 at 12:12

@Boneist when i use to_char() in this case, i m getting the value as 0.00 surprisingly. I m using this query in a script with calls to the oracle db. Any idea about what might be wrong.

– Satyanvesh D
Nov 21 '18 at 14:23

in a script with calls to the oracle db what kind of script? A unix script? A SQLPlus script? Something else? What happens if you run the query directly against the database (e.g. in SQLPLus/Toad/SQL Developer/etc)?

– Boneist
Nov 21 '18 at 14:32

add a comment |

2 Answers
2

active

oldest

votes

You may use such a formatting :

select to_char(-156.57,'fm0000000000000D00','NLS_NUMERIC_CHARACTERS = ''.,''')

       as Result

  from dual;



RESULT

-----------------

-0000000000156.57

answered Nov 21 '18 at 12:10

Barbaros Özhan

13.3k71633

Thanks for the answer. Basically i m using this query in a shell script. But when i use to_char () with whatever formatting, i m getting the result as 0.00 surprisingly. Any idea.

– Satyanvesh D
Nov 21 '18 at 14:22

@SatyanveshD welcome. Maybe you assign the result to a variable with insufficient length, in your script ... Maybe of type number( x, 2) ...

– Barbaros Özhan
Nov 21 '18 at 15:15

thank you so much. yeah your logic was correct. In the script, the result was getting truncated due to assigning it to different variable. I have gone through the entire script and removed that now. Its working fine with to_char function now. Thanks.

– Satyanvesh D
Nov 22 '18 at 7:43

@SatyanveshD you're welcome dear friend.

– Barbaros Özhan
Nov 22 '18 at 8:00

add a comment |

  select to_char(-156.57,'000000000.00')

  from dual;

answered Nov 22 '18 at 4:52

nikhil sugandh

1,2762719

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53411625%2fhow-to-preserve-leading-zeros-when-converting-to-a-decimal-in-oracle%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

You may use such a formatting :

select to_char(-156.57,'fm0000000000000D00','NLS_NUMERIC_CHARACTERS = ''.,''')

       as Result

  from dual;



RESULT

-----------------

-0000000000156.57

answered Nov 21 '18 at 12:10

Barbaros Özhan

13.3k71633

Thanks for the answer. Basically i m using this query in a shell script. But when i use to_char () with whatever formatting, i m getting the result as 0.00 surprisingly. Any idea.

– Satyanvesh D
Nov 21 '18 at 14:22

@SatyanveshD welcome. Maybe you assign the result to a variable with insufficient length, in your script ... Maybe of type number( x, 2) ...

– Barbaros Özhan
Nov 21 '18 at 15:15

thank you so much. yeah your logic was correct. In the script, the result was getting truncated due to assigning it to different variable. I have gone through the entire script and removed that now. Its working fine with to_char function now. Thanks.

– Satyanvesh D
Nov 22 '18 at 7:43

@SatyanveshD you're welcome dear friend.

– Barbaros Özhan
Nov 22 '18 at 8:00

add a comment |

You may use such a formatting :

select to_char(-156.57,'fm0000000000000D00','NLS_NUMERIC_CHARACTERS = ''.,''')

       as Result

  from dual;



RESULT

-----------------

-0000000000156.57

answered Nov 21 '18 at 12:10

Barbaros Özhan

13.3k71633

Thanks for the answer. Basically i m using this query in a shell script. But when i use to_char () with whatever formatting, i m getting the result as 0.00 surprisingly. Any idea.

– Satyanvesh D
Nov 21 '18 at 14:22

@SatyanveshD welcome. Maybe you assign the result to a variable with insufficient length, in your script ... Maybe of type number( x, 2) ...

– Barbaros Özhan
Nov 21 '18 at 15:15

thank you so much. yeah your logic was correct. In the script, the result was getting truncated due to assigning it to different variable. I have gone through the entire script and removed that now. Its working fine with to_char function now. Thanks.

– Satyanvesh D
Nov 22 '18 at 7:43

@SatyanveshD you're welcome dear friend.

– Barbaros Özhan
Nov 22 '18 at 8:00

add a comment |

You may use such a formatting :

select to_char(-156.57,'fm0000000000000D00','NLS_NUMERIC_CHARACTERS = ''.,''')

       as Result

  from dual;



RESULT

-----------------

-0000000000156.57

answered Nov 21 '18 at 12:10

Barbaros Özhan

13.3k71633

You may use such a formatting :

select to_char(-156.57,'fm0000000000000D00','NLS_NUMERIC_CHARACTERS = ''.,''')

       as Result

  from dual;



RESULT

-----------------

-0000000000156.57

answered Nov 21 '18 at 12:10

Barbaros Özhan

13.3k71633

answered Nov 21 '18 at 12:10

Barbaros Özhan

13.3k71633

answered Nov 21 '18 at 12:10

Barbaros Özhan

13.3k71633

answered Nov 21 '18 at 12:10

Barbaros Özhan

13.3k71633

Thanks for the answer. Basically i m using this query in a shell script. But when i use to_char () with whatever formatting, i m getting the result as 0.00 surprisingly. Any idea.

– Satyanvesh D
Nov 21 '18 at 14:22

@SatyanveshD welcome. Maybe you assign the result to a variable with insufficient length, in your script ... Maybe of type number( x, 2) ...

– Barbaros Özhan
Nov 21 '18 at 15:15

thank you so much. yeah your logic was correct. In the script, the result was getting truncated due to assigning it to different variable. I have gone through the entire script and removed that now. Its working fine with to_char function now. Thanks.

– Satyanvesh D
Nov 22 '18 at 7:43

@SatyanveshD you're welcome dear friend.

– Barbaros Özhan
Nov 22 '18 at 8:00

add a comment |

Thanks for the answer. Basically i m using this query in a shell script. But when i use to_char () with whatever formatting, i m getting the result as 0.00 surprisingly. Any idea.

– Satyanvesh D
Nov 21 '18 at 14:22

@SatyanveshD welcome. Maybe you assign the result to a variable with insufficient length, in your script ... Maybe of type number( x, 2) ...

– Barbaros Özhan
Nov 21 '18 at 15:15

thank you so much. yeah your logic was correct. In the script, the result was getting truncated due to assigning it to different variable. I have gone through the entire script and removed that now. Its working fine with to_char function now. Thanks.

– Satyanvesh D
Nov 22 '18 at 7:43

@SatyanveshD you're welcome dear friend.

– Barbaros Özhan
Nov 22 '18 at 8:00

Thanks for the answer. Basically i m using this query in a shell script. But when i use to_char () with whatever formatting, i m getting the result as 0.00 surprisingly. Any idea.

– Satyanvesh D
Nov 21 '18 at 14:22

@SatyanveshD welcome. Maybe you assign the result to a variable with insufficient length, in your script ... Maybe of type number( x, 2) ...

– Barbaros Özhan
Nov 21 '18 at 15:15

thank you so much. yeah your logic was correct. In the script, the result was getting truncated due to assigning it to different variable. I have gone through the entire script and removed that now. Its working fine with to_char function now. Thanks.

– Satyanvesh D
Nov 22 '18 at 7:43

@SatyanveshD you're welcome dear friend.

– Barbaros Özhan
Nov 22 '18 at 8:00

add a comment |

  select to_char(-156.57,'000000000.00')

  from dual;

answered Nov 22 '18 at 4:52

nikhil sugandh

1,2762719

add a comment |

  select to_char(-156.57,'000000000.00')

  from dual;

answered Nov 22 '18 at 4:52

nikhil sugandh

1,2762719

add a comment |

  select to_char(-156.57,'000000000.00')

  from dual;

answered Nov 22 '18 at 4:52

nikhil sugandh

1,2762719

  select to_char(-156.57,'000000000.00')

  from dual;

answered Nov 22 '18 at 4:52

nikhil sugandh

1,2762719

answered Nov 22 '18 at 4:52

nikhil sugandh

1,2762719

answered Nov 22 '18 at 4:52

nikhil sugandh

1,2762719

answered Nov 22 '18 at 4:52

nikhil sugandh

1,2762719

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu