replace .0 on varchar data type

Question

I have a varchar column that has numbers with .0

This column has both numeric data and non-numeric data.

I first tried to convert data type to integer, but since there is non-numeric data type, it would not let me.

How do I remove .0 (from all numbers that has .0)?

So, for example, 100.0 should be 100

I am not trying to use select, cast or truncate as I need to actually modify the existing data.

Thanks.

Answer 1

Since the column has both numeric and non-numeric data it is not enough to just check if it ends with '.0'.
You should also check if it is a numeric value, which can be done with TRY_CAST():

UPDATE tablename
SET col = LEFT(col, LEN(col) - 2)
WHERE col LIKE '%.0' AND TRY_CAST(col AS FLOAT) IS NOT NULL

See the demo.

Answer 2

Assuming you want to update your table...

• where x = your table name
• yourfieldname = the field name you need to update.

.

UPDATE table X 
SET yourfieldName = left(yourfieldname,len(yourieldName)-2) 
WHERE right(yourfieldName,2)='.0')
 -- or perhaps where yourfieldname like '%.0' would be faster...

Should: update all fields ending in .0 would need to test to see which where clause would be faster depending on indexes. if speed is a consideration. If not; and this is a 1 and done... does it matter?

Be sure to test on a subset/copy table!

Assumes you don't have a spaces after the .0... or any non-display characters.. If you do you'll need to trim off the spaces and replace the non-display characters with empty string ''