Decimal point is removed before update to character variable
I have data in the below format and I want to update a destination table column of type varchar2
with below values. But the problem is it updates as .462
instead of 0.462
by using trim with leading '0'
.
source destination column
----------------- ------------------
0000004.304300000 4.3043
0000005.504500000 5.5045
0000141.400000000 141.4
0000138.900000000 138.9
0000000.462000000 0.462
0000000.000297000 0.000297
sql oracle
add a comment |
I have data in the below format and I want to update a destination table column of type varchar2
with below values. But the problem is it updates as .462
instead of 0.462
by using trim with leading '0'
.
source destination column
----------------- ------------------
0000004.304300000 4.3043
0000005.504500000 5.5045
0000141.400000000 141.4
0000138.900000000 138.9
0000000.462000000 0.462
0000000.000297000 0.000297
sql oracle
3
Why, oh why are you storing numbers invarchar2
data type? Even if the source is not under your control, at least the target should be. Make the destination column anumber
column; if you must show those numbers with a specific format model on your reports (there is no other place where you must show numbers as strings, with a specific format model), you can do so when you create the reports.
– mathguy
Dec 31 '18 at 14:25
add a comment |
I have data in the below format and I want to update a destination table column of type varchar2
with below values. But the problem is it updates as .462
instead of 0.462
by using trim with leading '0'
.
source destination column
----------------- ------------------
0000004.304300000 4.3043
0000005.504500000 5.5045
0000141.400000000 141.4
0000138.900000000 138.9
0000000.462000000 0.462
0000000.000297000 0.000297
sql oracle
I have data in the below format and I want to update a destination table column of type varchar2
with below values. But the problem is it updates as .462
instead of 0.462
by using trim with leading '0'
.
source destination column
----------------- ------------------
0000004.304300000 4.3043
0000005.504500000 5.5045
0000141.400000000 141.4
0000138.900000000 138.9
0000000.462000000 0.462
0000000.000297000 0.000297
sql oracle
sql oracle
edited Dec 31 '18 at 14:08
Littlefoot
22.3k71533
22.3k71533
asked Dec 31 '18 at 12:47
kirthi kumarkirthi kumar
93
93
3
Why, oh why are you storing numbers invarchar2
data type? Even if the source is not under your control, at least the target should be. Make the destination column anumber
column; if you must show those numbers with a specific format model on your reports (there is no other place where you must show numbers as strings, with a specific format model), you can do so when you create the reports.
– mathguy
Dec 31 '18 at 14:25
add a comment |
3
Why, oh why are you storing numbers invarchar2
data type? Even if the source is not under your control, at least the target should be. Make the destination column anumber
column; if you must show those numbers with a specific format model on your reports (there is no other place where you must show numbers as strings, with a specific format model), you can do so when you create the reports.
– mathguy
Dec 31 '18 at 14:25
3
3
Why, oh why are you storing numbers in
varchar2
data type? Even if the source is not under your control, at least the target should be. Make the destination column a number
column; if you must show those numbers with a specific format model on your reports (there is no other place where you must show numbers as strings, with a specific format model), you can do so when you create the reports.– mathguy
Dec 31 '18 at 14:25
Why, oh why are you storing numbers in
varchar2
data type? Even if the source is not under your control, at least the target should be. Make the destination column a number
column; if you must show those numbers with a specific format model on your reports (there is no other place where you must show numbers as strings, with a specific format model), you can do so when you create the reports.– mathguy
Dec 31 '18 at 14:25
add a comment |
1 Answer
1
active
oldest
votes
A little bit of TO_CHAR
ing and TO_NUMBER
ing with appropriate format mask might do the job. Have a look at the example:
SQL> create table test (source varchar2 (20), destination varchar2(20));
Table created.
SQL> insert into test (source)
2 select '0000004.304300000' from dual union all
3 select '0000000.462000000' from dual union all
4 select '0000141.400000000' from dual union all
5 select '0000033.000000000' from dual;
4 rows created.
SQL> alter session set nls_numeric_characters = '.,';
Session altered.
SQL> update test set
2 destination = rtrim(to_char(to_number(source), 'fm999990D99999999'), '.');
4 rows updated.
SQL> select * From test;
SOURCE DESTINATION
-------------------- --------------------
0000004.304300000 4.3043
0000000.462000000 0.462
0000141.400000000 141.4
0000033.000000000 33
SQL>
1
Note though that this solution will rewrite the string'33'
as'33.'
- it is not clear if that's the OP wants (or needs?) Easy fix: wrap withinRTRIM(....., '.')
– mathguy
Dec 31 '18 at 14:28
Thank you, @mathguy. Fixed.
– Littlefoot
Dec 31 '18 at 14:34
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987696%2fdecimal-point-is-removed-before-update-to-character-variable%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
A little bit of TO_CHAR
ing and TO_NUMBER
ing with appropriate format mask might do the job. Have a look at the example:
SQL> create table test (source varchar2 (20), destination varchar2(20));
Table created.
SQL> insert into test (source)
2 select '0000004.304300000' from dual union all
3 select '0000000.462000000' from dual union all
4 select '0000141.400000000' from dual union all
5 select '0000033.000000000' from dual;
4 rows created.
SQL> alter session set nls_numeric_characters = '.,';
Session altered.
SQL> update test set
2 destination = rtrim(to_char(to_number(source), 'fm999990D99999999'), '.');
4 rows updated.
SQL> select * From test;
SOURCE DESTINATION
-------------------- --------------------
0000004.304300000 4.3043
0000000.462000000 0.462
0000141.400000000 141.4
0000033.000000000 33
SQL>
1
Note though that this solution will rewrite the string'33'
as'33.'
- it is not clear if that's the OP wants (or needs?) Easy fix: wrap withinRTRIM(....., '.')
– mathguy
Dec 31 '18 at 14:28
Thank you, @mathguy. Fixed.
– Littlefoot
Dec 31 '18 at 14:34
add a comment |
A little bit of TO_CHAR
ing and TO_NUMBER
ing with appropriate format mask might do the job. Have a look at the example:
SQL> create table test (source varchar2 (20), destination varchar2(20));
Table created.
SQL> insert into test (source)
2 select '0000004.304300000' from dual union all
3 select '0000000.462000000' from dual union all
4 select '0000141.400000000' from dual union all
5 select '0000033.000000000' from dual;
4 rows created.
SQL> alter session set nls_numeric_characters = '.,';
Session altered.
SQL> update test set
2 destination = rtrim(to_char(to_number(source), 'fm999990D99999999'), '.');
4 rows updated.
SQL> select * From test;
SOURCE DESTINATION
-------------------- --------------------
0000004.304300000 4.3043
0000000.462000000 0.462
0000141.400000000 141.4
0000033.000000000 33
SQL>
1
Note though that this solution will rewrite the string'33'
as'33.'
- it is not clear if that's the OP wants (or needs?) Easy fix: wrap withinRTRIM(....., '.')
– mathguy
Dec 31 '18 at 14:28
Thank you, @mathguy. Fixed.
– Littlefoot
Dec 31 '18 at 14:34
add a comment |
A little bit of TO_CHAR
ing and TO_NUMBER
ing with appropriate format mask might do the job. Have a look at the example:
SQL> create table test (source varchar2 (20), destination varchar2(20));
Table created.
SQL> insert into test (source)
2 select '0000004.304300000' from dual union all
3 select '0000000.462000000' from dual union all
4 select '0000141.400000000' from dual union all
5 select '0000033.000000000' from dual;
4 rows created.
SQL> alter session set nls_numeric_characters = '.,';
Session altered.
SQL> update test set
2 destination = rtrim(to_char(to_number(source), 'fm999990D99999999'), '.');
4 rows updated.
SQL> select * From test;
SOURCE DESTINATION
-------------------- --------------------
0000004.304300000 4.3043
0000000.462000000 0.462
0000141.400000000 141.4
0000033.000000000 33
SQL>
A little bit of TO_CHAR
ing and TO_NUMBER
ing with appropriate format mask might do the job. Have a look at the example:
SQL> create table test (source varchar2 (20), destination varchar2(20));
Table created.
SQL> insert into test (source)
2 select '0000004.304300000' from dual union all
3 select '0000000.462000000' from dual union all
4 select '0000141.400000000' from dual union all
5 select '0000033.000000000' from dual;
4 rows created.
SQL> alter session set nls_numeric_characters = '.,';
Session altered.
SQL> update test set
2 destination = rtrim(to_char(to_number(source), 'fm999990D99999999'), '.');
4 rows updated.
SQL> select * From test;
SOURCE DESTINATION
-------------------- --------------------
0000004.304300000 4.3043
0000000.462000000 0.462
0000141.400000000 141.4
0000033.000000000 33
SQL>
edited Dec 31 '18 at 14:34
answered Dec 31 '18 at 14:07
LittlefootLittlefoot
22.3k71533
22.3k71533
1
Note though that this solution will rewrite the string'33'
as'33.'
- it is not clear if that's the OP wants (or needs?) Easy fix: wrap withinRTRIM(....., '.')
– mathguy
Dec 31 '18 at 14:28
Thank you, @mathguy. Fixed.
– Littlefoot
Dec 31 '18 at 14:34
add a comment |
1
Note though that this solution will rewrite the string'33'
as'33.'
- it is not clear if that's the OP wants (or needs?) Easy fix: wrap withinRTRIM(....., '.')
– mathguy
Dec 31 '18 at 14:28
Thank you, @mathguy. Fixed.
– Littlefoot
Dec 31 '18 at 14:34
1
1
Note though that this solution will rewrite the string
'33'
as '33.'
- it is not clear if that's the OP wants (or needs?) Easy fix: wrap within RTRIM(....., '.')
– mathguy
Dec 31 '18 at 14:28
Note though that this solution will rewrite the string
'33'
as '33.'
- it is not clear if that's the OP wants (or needs?) Easy fix: wrap within RTRIM(....., '.')
– mathguy
Dec 31 '18 at 14:28
Thank you, @mathguy. Fixed.
– Littlefoot
Dec 31 '18 at 14:34
Thank you, @mathguy. Fixed.
– Littlefoot
Dec 31 '18 at 14:34
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987696%2fdecimal-point-is-removed-before-update-to-character-variable%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Why, oh why are you storing numbers in
varchar2
data type? Even if the source is not under your control, at least the target should be. Make the destination column anumber
column; if you must show those numbers with a specific format model on your reports (there is no other place where you must show numbers as strings, with a specific format model), you can do so when you create the reports.– mathguy
Dec 31 '18 at 14:25