How to truncate a real whole number?

[empress101]

Hi I kinda new to SQL Oracle stuff.

I am trying to get a list of zip codes from a table i have.

I have SELECT ZIP_CODES FROM ADDRESS;

It works but i get also ZIP CODES that have more than 5 numbers in it. I am trying to compare this column of zip codes with another column of zip codes in another table that only have 5 numbers for each code.

I want to strip out, cut off, or turncate the zip codes listed in the first table to only 5 numbers.

I have searched alot in books and web but all talk about the TRUNC command. But this will not work for me because that deals with decimal place stuff. There are no decimal in the zip codes I am trying to work with.

Any one know how to truncate, strip, cutoff ending numbers off a real , whole number.

Example: zip code 802540001 how do i remove the last four numbers? the 0001

thanks any help much appreciated.

 

[Turkbear]

Hi,
Convert it to text and use substring and convert it back to number ( if you need it as a number):

Zip5 = to_number(substr(to_char(ZIP_CODES),1,5))

May not be very efficient looking, but should do the job..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Empress,

I'm betting that your source column is VARCHAR2 and not NUMBER (Since ZIP codes for Boston, for example, will not work properly in a NUMBER column). Therefore, I suggest a simplification of Turkbear's excellent suggestion:

...Zip5 = substr(ZIP_CODES,1,5)...

Let us know if my guesses were wrong.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[empress101]

Thank you for help ....much appreciated.

SantaMufasa your guess was not wrong.

I have it working now ..i basically am comparing 2 columns of zip codes from different tables. Some have more than 5 numbers and I needed to just compare 5 to 5 so i could find the ones that did not match. Seems to work well now.

thanks again