SQL - Strip / Remove Lead Zeroes of a String

Cast the string to an int, then back into a nvarchar:


select CAST(CAST(substring('000422', 3, 4) as int) as nvarchar)