Search code examples
sqldata-warehousenetezza

Netezza code to convert data ending with minus symbol and instead having it start with minus

In a table I have numbers in the format of character datatype with both minus and plus figures. The issue is the ones having minus which looks like for instance 1.000- (instead of being -1.000) The ones that are plus is looking like 1.000 which is all fine hence only the ones supposed to be minus I need to correct.

Below code is the closest I have reached but problem is that when testing it everything becomes minus even if there are plus figures as well. It have been casted to int datatype from being character in order to be able to sum it.

cast('-' || SUBSTR(Numbers, 1, LENGTH(Numbers) - 1)as int)

So to summarize my question how can I have the character data in "Numbers" column to become -1 if its 1- and do nothing if its 1 in IBM Netezza database?

Thanks!

Solution

  • The to_number function in your friend in such cases

    select * from test;
   C1
--------
 1.000-
 21-
 1.2
 2
 100-
 1.23-
 213.01
(7 rows)

    Now we can convert it to the right text by doing

     select to_number(c1, '9999D999S') from test;
 TO_NUMBER
-----------
    -1.000
   -21.000
     1.200
     2.000
  -100.000
    -1.230
   213.010
(7 rows)