Remove Trailing Characters from a Number Column

There may be times when you got data from Third Source, which may be junk in some context as Suppose in Distance Column there could be a value of 8.5 km or in weight there may be a value of 444.90 kg. As these columns should be in number format. You should segregate those records and update them.,

Here is the script to find them:

SELECT Rno, Value, LEFT(Value,DATALENGTH(Value)-(PATINDEX('%[0-9]%',REVERSE(Value))-1)) AS ValueWithoutTrailChar
FROM tbl_RemoveCharacter

Script to remove trailing characters from Number column:

UPDATE tbl_RemoveCharacter
SET Value = LEFT(Value,DATALENGTH(Value)-(PATINDEX('%[0-9]%',REVERSE(Value))-1))



Happy Coding !!!