Oracle SQL - Round decimal to 2/3 based on conditon

up vote
0
down vote

favorite

Hello I have a requirement where I need to round decimals based on the condition:

If the first 2 digit after the decimals is not 00 then I need to round it to 2 place, otherwise 3 places.

Example:
1.232133342 => ROUND(1.232133342,2) //this will work

but if the number is 1.00233231213 value should be 1.002 //round to 3 digit

if the number is 1.0000223123312then the value should be 1.00002

Is there any function? Any help?

edited Nov 19 at 8:14

Barmar

414k34239340

asked Nov 19 at 7:46

user3721687

506

add a comment |

up vote
0
down vote

favorite

Hello I have a requirement where I need to round decimals based on the condition:

If the first 2 digit after the decimals is not 00 then I need to round it to 2 place, otherwise 3 places.

Example:
1.232133342 => ROUND(1.232133342,2) //this will work

but if the number is 1.00233231213 value should be 1.002 //round to 3 digit

if the number is 1.0000223123312then the value should be 1.00002

Is there any function? Any help?

edited Nov 19 at 8:14

Barmar

414k34239340

asked Nov 19 at 7:46

user3721687

506

add a comment |

up vote
0
down vote

favorite

Hello I have a requirement where I need to round decimals based on the condition:

If the first 2 digit after the decimals is not 00 then I need to round it to 2 place, otherwise 3 places.

Example:
1.232133342 => ROUND(1.232133342,2) //this will work

but if the number is 1.00233231213 value should be 1.002 //round to 3 digit

if the number is 1.0000223123312then the value should be 1.00002

Is there any function? Any help?

edited Nov 19 at 8:14

Barmar

414k34239340

asked Nov 19 at 7:46

user3721687

506

Hello I have a requirement where I need to round decimals based on the condition:

If the first 2 digit after the decimals is not 00 then I need to round it to 2 place, otherwise 3 places.

Example:
1.232133342 => ROUND(1.232133342,2) //this will work

but if the number is 1.00233231213 value should be 1.002 //round to 3 digit

if the number is 1.0000223123312then the value should be 1.00002

Is there any function? Any help?

sql oracle rounding truncate

edited Nov 19 at 8:14

Barmar

414k34239340

asked Nov 19 at 7:46

user3721687

506

edited Nov 19 at 8:14

Barmar

414k34239340

asked Nov 19 at 7:46

user3721687

506

edited Nov 19 at 8:14

Barmar

414k34239340

edited Nov 19 at 8:14

Barmar

414k34239340

edited Nov 19 at 8:14

Barmar

414k34239340

asked Nov 19 at 7:46

user3721687

506

asked Nov 19 at 7:46

user3721687

506

asked Nov 19 at 7:46

user3721687

506

add a comment |

2 Answers
2

active

oldest

votes

up vote
4
down vote

accepted

This rounds to at least two digits, maybe more by counting the number of leading zeros in the fractional part using a RegEx

round(x, greatest(2, length(regexp_substr(x,'.0*'))))

see db-fiddle

edited Nov 19 at 9:26

answered Nov 19 at 8:27

dnoeth

44k31838

What about his last condition? if the number is 1.0000223123312then the value should be 1.00002
– Nebi
Nov 19 at 8:33

@Nebi: Yep, the question was a bit misleading: round it to 2 place, otherwise 3 place. Fixed ;-)
– dnoeth
Nov 19 at 9:00

Yery nice answer! +1
– Nebi
Nov 19 at 9:12

@Nebi: Still way too complicated, there's no need for MOD/ABS :-)
– dnoeth
Nov 19 at 9:26

Awesome! working fine. thanks.
– user3721687
Nov 19 at 9:39

add a comment |

up vote
3
down vote

Subtract the floor of the number from the number, and check if it's less than .01.

CASE WHEN ABS(number) - FLOOR(ABS(number)) < .01 THEN ROUND(number, 3)

     ELSE ROUND(number, 2)

END

edited Nov 19 at 9:21

answered Nov 19 at 8:16

Barmar

414k34239340

This fails for negative values
– dnoeth
Nov 19 at 8:27

I added ABS() to fix that.
– Barmar
Nov 19 at 9:21

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',
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%2f53370305%2foracle-sql-round-decimal-to-2-3-based-on-conditon%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

up vote
4
down vote

accepted

This rounds to at least two digits, maybe more by counting the number of leading zeros in the fractional part using a RegEx

round(x, greatest(2, length(regexp_substr(x,'.0*'))))

see db-fiddle

edited Nov 19 at 9:26

answered Nov 19 at 8:27

dnoeth

44k31838

What about his last condition? if the number is 1.0000223123312then the value should be 1.00002
– Nebi
Nov 19 at 8:33

@Nebi: Yep, the question was a bit misleading: round it to 2 place, otherwise 3 place. Fixed ;-)
– dnoeth
Nov 19 at 9:00

Yery nice answer! +1
– Nebi
Nov 19 at 9:12

@Nebi: Still way too complicated, there's no need for MOD/ABS :-)
– dnoeth
Nov 19 at 9:26

Awesome! working fine. thanks.
– user3721687
Nov 19 at 9:39

add a comment |

up vote
4
down vote

accepted

This rounds to at least two digits, maybe more by counting the number of leading zeros in the fractional part using a RegEx

round(x, greatest(2, length(regexp_substr(x,'.0*'))))

see db-fiddle

edited Nov 19 at 9:26

answered Nov 19 at 8:27

dnoeth

44k31838

What about his last condition? if the number is 1.0000223123312then the value should be 1.00002
– Nebi
Nov 19 at 8:33

@Nebi: Yep, the question was a bit misleading: round it to 2 place, otherwise 3 place. Fixed ;-)
– dnoeth
Nov 19 at 9:00

Yery nice answer! +1
– Nebi
Nov 19 at 9:12

@Nebi: Still way too complicated, there's no need for MOD/ABS :-)
– dnoeth
Nov 19 at 9:26

Awesome! working fine. thanks.
– user3721687
Nov 19 at 9:39

add a comment |

up vote
4
down vote

accepted

This rounds to at least two digits, maybe more by counting the number of leading zeros in the fractional part using a RegEx

round(x, greatest(2, length(regexp_substr(x,'.0*'))))

see db-fiddle

edited Nov 19 at 9:26

answered Nov 19 at 8:27

dnoeth

44k31838

This rounds to at least two digits, maybe more by counting the number of leading zeros in the fractional part using a RegEx

round(x, greatest(2, length(regexp_substr(x,'.0*'))))

see db-fiddle

edited Nov 19 at 9:26

answered Nov 19 at 8:27

dnoeth

44k31838

edited Nov 19 at 9:26

answered Nov 19 at 8:27

dnoeth

44k31838

answered Nov 19 at 8:27

dnoeth

44k31838

answered Nov 19 at 8:27

dnoeth

44k31838

What about his last condition? if the number is 1.0000223123312then the value should be 1.00002
– Nebi
Nov 19 at 8:33

@Nebi: Yep, the question was a bit misleading: round it to 2 place, otherwise 3 place. Fixed ;-)
– dnoeth
Nov 19 at 9:00

Yery nice answer! +1
– Nebi
Nov 19 at 9:12

@Nebi: Still way too complicated, there's no need for MOD/ABS :-)
– dnoeth
Nov 19 at 9:26

Awesome! working fine. thanks.
– user3721687
Nov 19 at 9:39

add a comment |

What about his last condition? if the number is 1.0000223123312then the value should be 1.00002
– Nebi
Nov 19 at 8:33

@Nebi: Yep, the question was a bit misleading: round it to 2 place, otherwise 3 place. Fixed ;-)
– dnoeth
Nov 19 at 9:00

Yery nice answer! +1
– Nebi
Nov 19 at 9:12

@Nebi: Still way too complicated, there's no need for MOD/ABS :-)
– dnoeth
Nov 19 at 9:26

Awesome! working fine. thanks.
– user3721687
Nov 19 at 9:39

What about his last condition? if the number is 1.0000223123312then the value should be 1.00002
– Nebi
Nov 19 at 8:33

@Nebi: Yep, the question was a bit misleading: round it to 2 place, otherwise 3 place. Fixed ;-)
– dnoeth
Nov 19 at 9:00

Yery nice answer! +1
– Nebi
Nov 19 at 9:12

@Nebi: Still way too complicated, there's no need for MOD/ABS :-)
– dnoeth
Nov 19 at 9:26

Awesome! working fine. thanks.
– user3721687
Nov 19 at 9:39

add a comment |

up vote
3
down vote

Subtract the floor of the number from the number, and check if it's less than .01.

CASE WHEN ABS(number) - FLOOR(ABS(number)) < .01 THEN ROUND(number, 3)

     ELSE ROUND(number, 2)

END

edited Nov 19 at 9:21

answered Nov 19 at 8:16

Barmar

414k34239340

This fails for negative values
– dnoeth
Nov 19 at 8:27

I added ABS() to fix that.
– Barmar
Nov 19 at 9:21

add a comment |

up vote
3
down vote

Subtract the floor of the number from the number, and check if it's less than .01.

CASE WHEN ABS(number) - FLOOR(ABS(number)) < .01 THEN ROUND(number, 3)

     ELSE ROUND(number, 2)

END

edited Nov 19 at 9:21

answered Nov 19 at 8:16

Barmar

414k34239340

This fails for negative values
– dnoeth
Nov 19 at 8:27

I added ABS() to fix that.
– Barmar
Nov 19 at 9:21

add a comment |

up vote
3
down vote

Subtract the floor of the number from the number, and check if it's less than .01.

CASE WHEN ABS(number) - FLOOR(ABS(number)) < .01 THEN ROUND(number, 3)

     ELSE ROUND(number, 2)

END

edited Nov 19 at 9:21

answered Nov 19 at 8:16

Barmar

414k34239340

Subtract the floor of the number from the number, and check if it's less than .01.

CASE WHEN ABS(number) - FLOOR(ABS(number)) < .01 THEN ROUND(number, 3)

     ELSE ROUND(number, 2)

END

edited Nov 19 at 9:21

answered Nov 19 at 8:16

Barmar

414k34239340

edited Nov 19 at 9:21

answered Nov 19 at 8:16

Barmar

414k34239340

answered Nov 19 at 8:16

Barmar

414k34239340

answered Nov 19 at 8:16

Barmar

414k34239340

This fails for negative values
– dnoeth
Nov 19 at 8:27

I added ABS() to fix that.
– Barmar
Nov 19 at 9:21

add a comment |

This fails for negative values
– dnoeth
Nov 19 at 8:27

I added ABS() to fix that.
– Barmar
Nov 19 at 9:21

This fails for negative values
– dnoeth
Nov 19 at 8:27

I added ABS() to fix that.
– Barmar
Nov 19 at 9:21

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

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

搜尋此網誌

Argthtjtr