TSQL - Removing leading zeroes

select

      substring(ColumnName, patindex('%[^0]%',ColumnName), 8000)

from

      (

            select '00010A0' as ColumnName

            union all select 'sdfa'

            union all select '00001'

      ) as V1

;