need help with a simple SQL update statement.

Question

There's a field with type of varchar. It actually stores a float point string. Like 2.0 , 12.0 , 34.5 , 67.50 ... What I need is a update statement that remove the ending zeros of fields like 2.0 , 12.0 , change them to their integer representation , that is 2 , 12 ...,and leave 3.45 , 67.50 unchanged . How should I do this ? I am using oracle 10.

Answer 1

Does this work?

UPDATE Table Set Field = CAST(CAST(CAST(Field AS float) AS int) AS varchar(128));

Answer 2

As long as the only data in the fields are numbers then something like this should do it...

UPDATE <table>
   SET not_string = TRIM( TO_CHAR( TO_NUMBER( not_string ), '999999999999' ) )
 WHERE TO_NUMBER( not_string ) = TRUNC( TO_NUMBER( not_string ) )

The WHERE clause should limit the update to integers and the SET part converts the VARCHAR2 to a number and then back to a VARCHAR with the required formatting (change the number of 9s as required or rely on the NLS settings) for string back in the field.

Also, because most people are (or at least should be) thinking it..

If the field is a number then store it in the right datatype, formatting can be altered on output and this kind of string parsing could be avoided!