Remove Trailing Character from Number Column in sql server

Sometime in age or weight column we are getting values years or KM , we need to remove the character and  displace the numeric value and we will do the manipulation on that.

I am creating a table

CREATE TABLE tblEmp (                 EmpNo int,                 Name varchar(50),                 EmpAddress varchar(100),                 Mobile varchar(10),                 EmpAge varchar(10),                 EmpHegiht varchar(10),                 Empweight varchar(10) )

Now I am inserting some records.

INSERT INTO tblEmp VALUES (1,'Bagesh Kumar','Pune','888880XXXX','28 years','163 cm','78.5 kg') INSERT INTO tblEmp VALUES (2,'Rajesh Kumar','Patna','888880XXXX','25 years','153 cm','68.5 kg') INSERT INTO tblEmp VALUES (3,'Umesh Kumar','Chennai','888880XXXX','22 years','177 cm','88.5 kg') INSERT INTO tblEmp VALUES (4,'Ganesh Kumar','Kanpur','888880XXXX','26','163 cm','50 kg') INSERT INTO tblEmp VALUES (5,'Ajit Kumar','Mumbai','888880XXXX','20 years','173 cm','75')

See the table value

 

Use below sql script

SELECT                  EmpNo,Name,EmpAddress,Mobile                 ,EmpAge                 ,LEFT(EmpAge,DATALENGTH(EmpAge)-(PATINDEX('%[0-9]%',REVERSE(EmpAge))-1)) AS [Age in Numeric],                 EmpHegiht,                 LEFT(EmpHegiht,DATALENGTH(EmpHegiht)-(PATINDEX('%[0-9]%',REVERSE(EmpHegiht))-1)) AS [Height in Numeric],                 Empweight,                 LEFT(Empweight,DATALENGTH(Empweight)-(PATINDEX('%[0-9]%',REVERSE(Empweight))-1)) AS [weight in Numeric] FROM tblEmp

See the output

 

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.