substring

Try:

SUBSTRING(number, PATINDEX('%[^0]%', number), LEN(number) - PATINDEX('_0%', reverse(number))*2 - PATINDEX('%[^0]%', number)+1)

Hope this helps.