Try this modification of Jeff's suggestion. It accounts for the possibility of multiple alpha characters at the beginning of the string

DECLARE @SomeString CHAR(10);

SELECT @SomeString = 'XY00001250';

SELECT LEFT(@SomeString,CHARINDEX('0',@SomeString,1)-1)+RIGHT(RIGHT(@SomeString,LEN(@SomeString)-(CHARINDEX('0',@SomeString,1)))+0,9)

;

Elegant! Thanks, I learned something new today.

LEFT('X000001250',1) + RIGHT('X000001250',CHARINDEX('0',REVERSE('X000001250'),2)-1) will work to remove the mid-string zeros for a value following the X + 9 digit format.