Home » SQL & PL/SQL » SQL & PL/SQL » how to truncate a specific number range (pl/sql)
how to truncate a specific number range [message #341614] Tue, 19 August 2008 09:51 Go to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

I have variable called total_hrs when total_hrs go over a specific number (1,250,000) I need to truncate the number some kind of way and only use 6 digits (250,000) any ideas of how to truncate a 7 digit number to a 6 digit.

Thanks in advance

[Updated on: Tue, 19 August 2008 09:54]

Report message to a moderator

Re: how to truncate a specific number range [message #341615 is a reply to message #341614] Tue, 19 August 2008 09:58 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
It depends on what kind of rules you want to follow, if you just want the right six digits convert to a string and look into a combination of LENGTH and SUBSTR functions.
Re: how to truncate a specific number range [message #341618 is a reply to message #341615] Tue, 19 August 2008 10:06 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

So your saying take the number and convert it to a string compare the length if its greater than 7 (250,000 = 7) then what would be the starting postion of the substr?
Re: how to truncate a specific number range [message #341619 is a reply to message #341614] Tue, 19 August 2008 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MOD

Regards
Michel
Re: how to truncate a specific number range [message #341620 is a reply to message #341614] Tue, 19 August 2008 10:10 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
If it is a number, I would rather count the remainder of total_hrs divided by million (MOD function in Oracle).

Too slow, beaten by Michel Smile

[Updated on: Tue, 19 August 2008 10:12]

Report message to a moderator

Re: how to truncate a specific number range [message #341624 is a reply to message #341620] Tue, 19 August 2008 10:19 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Faster to to do it with MOD, would not have thought that.
Re: how to truncate a specific number range [message #341627 is a reply to message #341624] Tue, 19 August 2008 10:31 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Thanks everyone for your replies but what if its not a million it could be 2 5, 10 million maybe even a billion one day. Can the MOD function still be used, how does MOD work?
Re: how to truncate a specific number range [message #341628 is a reply to message #341627] Tue, 19 August 2008 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how does MOD work?

Click on the link I posted and read.

Regards
Michel
Re: how to truncate a specific number range [message #341629 is a reply to message #341614] Tue, 19 August 2008 10:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> how does MOD work?

Counts the remainder of one number divided by second number. Only maths, described e.g. in http://en.wikipedia.org/wiki/Remainder.
If you divide by million, you can get only numbers from 0 to 999999 (up to 6 digits, no longer).

> but what if its not a million it could be 2 5, 10 million maybe even a billion one day

What is "it"?
Re: how to truncate a specific number range [message #341631 is a reply to message #341629] Tue, 19 August 2008 11:12 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

"It " is the total_hrs, this is a sum of hours over a range of years, Im just thinking maybe this number, total_hrs, may reach 10 million one day.
Re: how to truncate a specific number range [message #341632 is a reply to message #341614] Tue, 19 August 2008 11:21 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Im just thinking maybe this number, total_hrs, may reach 10 million one day.

And what is the problem? You will still get last 6 digits, as this test case shows:
SQL> select mod( 87654321, 1000000 ) from dual;

MOD(87654321,1000000)
---------------------
               654321

SQL> 


[Edit: Corrected test case to 8-digit number]

[Updated on: Tue, 19 August 2008 11:23]

Report message to a moderator

Previous Topic: Deleting a row but keeping the Foreign Key
Next Topic: Need Assistance
Goto Forum:
  


Current Time: Wed Dec 07 06:36:52 CST 2016

Total time taken to generate the page: 0.07590 seconds