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