CREATE TABLE #Temp (Id int IDENTITY(1,1), PartNum varchar(5))

INSERT #Temp (PartNum) VALUES
('12345'),
('00123'),
('10000'),
('A1234'),
('12A34'),
('0A123'),
('000A1'),
('00D21'),
('00E33');

SELECT PartNum,
   CASE
      WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', PartNum) = 1 THEN CAST(CAST(PartNum AS int) AS varchar(5))
      ELSE PartNum
   END AS LeadingZerosStripped
FROM #Temp;

CREATE TABLE #Temp (Id int IDENTITY(1,1), PartNum varchar(25))

INSERT #Temp (PartNum) VALUES
('12345'),
('00123'),
('10000'),
('A1234'),
('12A34'),
('0A123'),
('000A1'),
('00D21'),
('00E33'),
('00000000000000000004560');

--SELECT PartNum,
--   CASE
--      WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', PartNum) = 1 THEN CAST(CAST(PartNum AS int) AS varchar(5))
--      ELSE PartNum
--   END AS LeadingZerosStripped
--FROM #Temp;

select *, patindex('%[^0]%', PartNum), len(PartNum), substring(PartNum, patindex('%[^0]%', PartNum), len(PartNum) - patindex('%[^0]%', PartNum) + 1) from #Temp;

drop table #Temp;

CREATE TABLE #Temp (Id int IDENTITY(1,1), PartNum varchar(25))

INSERT #Temp (PartNum) VALUES
('12345'),
('00123'),
('10000'),
('A1234'),
('12A34'),
('0A123'),
('000A1'),
('00D21'),
('00E33'),
('00000000000000000004560');

select
    *,
    case when patindex('%[^0-9]%', PartNum) = 0
        then substring(PartNum, patindex('%[^0]%', PartNum), len(PartNum) - patindex('%[^0]%', PartNum) + 1)
        else PartNum
    end
from
    #Temp;

drop table #Temp;

SELECT PartNum, RIGHT(PartNum, 
    LEN(PartNum) + CASE PATINDEX('%[^0-9]%', PartNum) 
        WHEN 0 THEN 1 - PATINDEX('%[^0]%', PartNum) ELSE 0 END)
FROM #Temp