The easiest way to do this is multiply by 1, but if it is a (n)varchar data type, it is a little more challenging.
This was the query I had to run:
DECLARE @insurancenumber nvarchar(100),@charactertopurge nvarchar(1)
SET @charactertopurge='0'
SET @insurancenumber='00PK102947'
SELECT @GMPI=GMPI
from Patient.dbo.PatientAlias
where AliasField=substring(@insurancenumber, patindex('%[^'+@charactertopurge+']%',@insurancenumber), len(@insurancenumber))