Maybe something like this, it removes all leading zeroes except where a value is all zeroes:


CREATE TABLE #Inventory(
  ItemNum nvarchar(400),
  action_desc varchar(100)
);

INSERT INTO #Inventory
VALUES
  ('143545043', 'All digits - No leading zeros'),
  (  '345230', 'All digits - No leading zeros'),
  ('00345230', 'All digits - Leading zeros - repeated value'),
  ('012234567', 'All digits - Leading zeros - unique value'),
  ('001542AS1', 'Alphanumeric - Leading zeros - unique value'),
  ('ASDF123', 'Alphanumeric - No leading zeros'),
  ('0000000', 'Do nothing - All zeros');

SELECT *, PATINDEX('%[^0]%',i.ItemNum) FROM #Inventory i;

UPDATE i SET
  ItemNum  = stuff(ItemNum,1,PATINDEX('%[^0]%',i.ItemNum),'')
FROM #Inventory i
WHERE
  i.ItemNum like '0%'
  AND PATINDEX('%[^0]%',i.ItemNum) > 0;

SELECT * FROM #Inventory;

GO
DROP TABLE #Inventory;
GO