replace .0 on varchar data type

user_3842845 0
Viewed 178

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.

enter image description here

sql-like sql-update tsql sql-server
3 Answers

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 ''

user_10164350

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.

user_104988280

Just another option

Example

Declare @YourTable table (SomeCol varchar(50))
Insert Into @YourTable values
 ('100.0')
,('1001.0')
,('Not Numeric')
,('-200.05')
,('10,250.0')

Update @YourTable
   set SomeCol = format(try_convert(money,SomeCol),'#.######')
 From @YourTable
 Where try_convert(money,SomeCol) is not null

The Updated Table

SomeCol
100
1001
Not Numeric
-200.05
10250
user_15700000
Related