OraFAQ Forum: SQL & PL/SQL » Next multiple of 10 value for the given integer

Home » SQL & PL/SQL » SQL & PL/SQL » Next multiple of 10 value for the given integer (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Next multiple of 10 value for the given integer [message #595314]

Tue, 10 September 2013 02:35

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All ,

Is there any function which return the next multiples of 10 value directly ?

Ex : input - 11 then it should return 20
156 - 160
299 - 300
43 - 50

Thanks
Saipradyumn

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595315 is a reply to message #595314]

Tue, 10 September 2013 02:39

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Divide the number by 10, round it up (ceil function will do that) Multiply the result of that by 10

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595316 is a reply to message #595314]

Tue, 10 September 2013 02:41

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

saipradyumn wrote on Tue, 10 September 2013 13:05

Is there any function which return the next multiples of 10 value directly ?

No such inbuilt function, however, you can write a SQL to achieve your task. Should be quite simple.

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595317 is a reply to message #595314]

Tue, 10 September 2013 02:42

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You can use CEIL, which rounds up to the next integer. If you divide your value by ten, apply ceil and multiply again, you get your desired output.

SQL> With mydata As
  2  ( Select  11 thevalue from dual union all
  3    Select 156          from dual union all
  4    Select 199          from dual union all
  5    Select  44          from dual
  6  )
  7  Select thevalue
  8       , ceil(thevalue/10)*10 roundup
  9  From   mydata
 10  /

  THEVALUE    ROUNDUP
---------- ----------
        11         20
       156        160
       199        200
        44         50

MHE

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595321 is a reply to message #595317]

Tue, 10 September 2013 02:59

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please let OP a chance to learn.

Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Regards
Michel

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595326 is a reply to message #595321]

Tue, 10 September 2013 03:13

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Michel, I'm puzzled. What can you teach him by not replying? How can you involve the OP without posting a reply?

MHE

[Updated on: Tue, 10 September 2013 03:13]

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595332 is a reply to message #595326]

Tue, 10 September 2013 04:10

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I meant you could give her only the first sentence without spoonfeeding her with the code.

(In addition, with more than 300 posts and 2 years here she would know she have to post a test case with her question.)

Regards
Michel

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595354 is a reply to message #595332]

Tue, 10 September 2013 09:48

Bill B
Messages: 1971
Registered: December 2004

Senior Member

just to show that theirs always more ways to skin a cat in oracle.

1* select round(241,-1) + 10 from dual;

ROUND(241,-1)+10
----------------
250

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595362 is a reply to message #595354]

Tue, 10 September 2013 11:02

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Close but not exactly:

SQL> select round(245,-1) + 10 from dual;
ROUND(245,-1)+10
----------------
             260

Regards
Michel

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595365 is a reply to message #595354]

Tue, 10 September 2013 12:01

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

Another way to skin the cat (what a horrible phrase that is):

orclz>
orclz> select 241 +10 - mod((241+10),10) from dual;

241+10-MOD((241+10),10)
-----------------------
                    250

orclz>

--update, jw

or better,

orclz> select 241 + 10 - mod(241,10) from dual;

241+10-MOD(241,10)
------------------
               250

orclz>

[Updated on: Tue, 10 September 2013 12:09]

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595367 is a reply to message #595365]

Tue, 10 September 2013 12:15

ebrian
Messages: 2794
Registered: April 2006

Senior Member

The cats keep coming (or are we just beating a dead horse)?? Too many animal references!

Anyway:

SQL> With mydata As
  2  ( Select  11 thevalue from dual union all
  3    Select 156          from dual union all
  4    Select 199          from dual union all
  5    Select  44          from dual
  6  )
  7  Select thevalue
  8       , trunc(thevalue,-1)+10 roundup
  9  From   mydata
 10  /

  THEVALUE    ROUNDUP
---------- ----------
        11         20
       156        160
       199        200
        44         50

Thanks for the code Maaher!

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595368 is a reply to message #595367]

Tue, 10 September 2013 12:37

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

And here's my latest attempt to boil the bunny:

orclz> ed
Wrote file afiedt.buf

  1  With mydata As
  2      ( Select  11 thevalue from dual union all
  3        Select 156          from dual union all
  4        Select 199          from dual union all
  5        Select  44          from dual
  6      )
  7      Select thevalue
  8           , to_number(substr(to_char(thevalue),1,length(to_char(thevalue)) - 1)||'0')+10
  9*     From   mydata
orclz> /

  THEVALUE TO_NUMBER(SUBSTR(TO_CHAR(THEVALUE),1,LENGTH(TO_CHAR(THEVALUE))-1)||'0')+10
---------- --------------------------------------------------------------------------
        11                                                                         20
       156                                                                        160
       199                                                                        200
        44                                                                         50

orclz>

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595395 is a reply to message #595368]

Tue, 10 September 2013 18:34

ebrian
Messages: 2794
Registered: April 2006

Senior Member

There was actually a problem with my previous cat skinning when the number was either negative and/or a number already a multiple of 10, but I believe this one should work for all situations:

SQL> With mydata As
  2    ( Select 10 val from dual union all
  3      Select -21  from dual union all
  4      Select -35.2  from dual union all
  5      Select 156.1  from dual union all
  6      Select 199    from dual union all
  7      Select  44    from dual
  8    )
  9    Select val
 10         , decode(sign(val), '-1', -1*floor(abs(val)/10), ceil(val/10))*10 roundup
 11    From   mydata
 12  /

       VAL    ROUNDUP
---------- ----------
        10         10
       -21        -20
     -35.2        -30
     156.1        160
       199        200
        44         50

6 rows selected.

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595411 is a reply to message #595395]

Wed, 11 September 2013 01:41

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

(Catskinners, you'd better not be seen by Barbara!)

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595413 is a reply to message #595395]

Wed, 11 September 2013 01:55

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

Of all the solutions, I think the ebrian(modified) algorithm is the best in that it handles fractions and both positive and negative values. However, the handling of negatives requires further modification if the results are conform to the generally accepted standard of "round away from zero":

  1  With mydata As
  2        ( Select 10 val from dual union all
  3          Select -21  from dual union all
  4          Select -35.2  from dual union all
  5          Select 156.1  from dual union all
  6          Select 199    from dual union all
  7          Select  44    from dual
  8        )
  9        Select val
 10            , decode(sign(val), '-1', -1*ceil(abs(val)/10), ceil(val/10))*10 roundup
 11*      From   mydata
orclz> /

       VAL    ROUNDUP
---------- ----------
        10         10
       -21        -30
     -35.2        -40
     156.1        160
       199        200
        44         50

6 rows selected.

orclz>

This has to be the best solution. Though as Sai has not responded to any of this, I suppose she has lost interest. Or perhaps she found the references to domestic animals to be little off-putting.

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595575 is a reply to message #595413]

Wed, 11 September 2013 16:49

ebrian
Messages: 2794
Registered: April 2006

Senior Member

John Watson wrote on Wed, 11 September 2013 02:55

...if the results are conform to the generally accepted standard of "round away from zero":

Yeah, I wasn't sure what protocol the OP was following for that.

John Watson wrote on Wed, 11 September 2013 02:55

Though as Sai has not responded to any of this, I suppose she has lost interest. Or perhaps she found the references to domestic animals to be little off-putting.

Littlefoot wrote on Wed, 11 September 2013 02:41

(Catskinners, you'd better not be seen by Barbara!)

Those are pretty good!

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595586 is a reply to message #595575]

Thu, 12 September 2013 01:35

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Thanks one & all for your valuable replies

Report message to a moderator

Previous Topic:	using regexp_replace to eliminate duplicate string with delimeter
Next Topic:	insert select in PLSQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 26 16:54:54 CDT 2025