This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a TSQL script to remove the trailing characters from the Number value in SQL Server.
Before a few days ago, I did a data migration from old CSV files to SQL Server table and found few number values with the junk of trailing characters like km, kg.
Those trailing characters need to be removed, so I prepared a TSQL script to perform this task.
Create the sample table and data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE tbl_RemoveCharacter ( Rno INTEGER ,Value VARCHAR(255) ) GO INSERT INTO tbl_RemoveCharacter VALUES (1,'8.5 km') INSERT INTO tbl_RemoveCharacter VALUES (2,'452.3 kg') INSERT INTO tbl_RemoveCharacter VALUES (3,'808') INSERT INTO tbl_RemoveCharacter VALUES (4,'102.5 km') INSERT INTO tbl_RemoveCharacter VALUES (5,NULL) GO |
Select your number with any trailing characters:
|
1 2 3 4 5 6 |
SELECT Rno ,Value ,LEFT(Value,DATALENGTH(Value)-(PATINDEX('%[0-9]%',REVERSE(Value))-1)) AS ValueWithoutTrailChar FROM tbl_RemoveCharacter GO |
The Result:
Script to remove trailing characters from Number column:
|
1 2 |
UPDATE tbl_RemoveCharacter SET Value = LEFT(Value,DATALENGTH(Value)-(PATINDEX('%[0-9]%',REVERSE(Value))-1)) |
