Remove a character from a number

Using Netezza,

I am trying to remove a '.' from a number.

Here is what I have:

45.01

What I want returned is:

4501

I have already tried the replace function with no success. Example: replace(DB_COLUMN, '.', '')

Can anyone help?
Erik HauserSenior Technical ConsultantAsked:
Who is Participating?
 
sdstuberCommented:
Since it's only a single character, you could try the TRANSLATE function
I believe (haven't tested) this works the same in Netezza and Oracle

translate(db_column,'a.','a')


the "a" is just a dummy character to act as a base for the translation mapping
the key part is to have the "." map to nothing.
0
 
slightwv (䄆 Netminder) Commented:
>>replace function with no success. Example: replace(DB_COLUMN, '.', '')

What did it return?  Should work.

Try this:
db_column*100
0
 
sdstuberCommented:
replace isn't part of netezza's syntax by default

you'll need the sql extention toolkit which can be downloaded from IBM's support site
0
 
slightwv (䄆 Netminder) Commented:
>>replace isn't part of netezza's syntax by default

I missed the Netezza piece.  Was only going by Oracle topic area.
0
 
Erik HauserSenior Technical ConsultantAuthor Commented:
Translate worked. Thank you!

My apologies for posting in the wrong area as there is no topic area for Netezza and Netezza somewhat closely mirrors Oracle.

Problem solved!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.