Search code examples
sqldigits

Change certain digits in int column

I have a table that looks like this:

tablename
  Id PRIMARY INT,
  value INT

The value columns holds all kind of different values and to clean some things up I need to edit the values so that the last 2 digits are always 0, example:

old       new 
17364  -> 17300
4958   -> 4900
12     -> 00
312948 -> 312900

Is there any way I can achieve this for all rows with an SQL query?

Solution

  • You can do this using integer division (by 100) and then multiplying again:

    update tablename
    set value = floor(value / 100) * 100;

    As a note: before executing the update, check that the values are what you want:

    select t.*, floor(value / 100) * 100 as new_value
from tablename t;