This user defined function is based on a function posted at this very useful site: http://www.sql-server-helper.com/functions/trim-leading-zeros.aspx.
I have modified it slightly to handle spaces within the input string; by first replacing any existing spaces with ‘¬’ (this seems to be a suitably obscure character that is never likely to occur in the input string).
CREATE FUNCTION RemoveLeadingZerosFromVarChar
-- Add the parameters for the function here
-- Declare the return variable here
DECLARE @Result varchar(max)
-- This function works by replacing all '0' characters with a ' ' (space)
-- character, then using LTRIM to strip all the leading spaces
-- Finally all ' ' characters remaining are changed back into '0' characters
-- (this restores any '0' characters indise the input string)
-- To handle actual spaces within the input string, first any spaces are
-- replaced with an obscure character that is never likely to occur ('¬')
-- so that this can be replaced with spaces again at the end.
SELECT @Result = replace(replace(ltrim(replace(
replace(ltrim(@inputStr), ' ', '¬') -- replace existing spaces with '¬'
, '0', ' ') -- replace '0' with ' '
) -- end of LTRIM to remove leading '0's that have been changed to ' 's
, ' ', '0') -- change ' ' back to '0'
, '¬', ' ') -- change '¬' back to ' '
-- Return the result of the function