T-SQL no leading zeroes

DECLARE @Sku NVARCHAR(500)

    SET @Sku = '000000000000000000000000000000000000000000000000000000012335670000'

 SELECT @Sku AS Original,

        SUBSTRING(@Sku,PATINDEX('%[^0]%',@Sku),DATALENGTH(@Sku)) AS NoLeadingZeros

Declare @MyStr char(20)

Set @MyStr = '00004567890'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

What this does is to find the first non-zero character in the field and then substring from there.  It also works if you have a dash imbeded in the field.

Declare @MyStr char(20)

Set @MyStr = '0000-45670'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))