SQL Server 2000 - force decimal value to 4 decimal places
Posted on 2009-05-04
Hi! The update statement below is based on a text string from which I am extracting pieces that I need to load into a database. I realized today that the value from characters 3 through 6 may have ending blanks rather than zeroes. For example, sometimes the value is 3.423 without the ending 0. This is causing the load to skip the value. Is there a way to change the line below to force ending zeroes if the value is, for example, "3.4 " or "3.42 " or "3.423 "? In these cases, I would want to have "3.4000", "3.4200" and "3.4230". If the value is "3.423", that's fine and nothing needs to be done.
set newvalue = cast(substring(t1.Column1,3,6) as decimal(5,4))