SQL(Query with numeric padding)

SELECT Cast(RIGHT('000000' + Rtrim ( Max(Cast(RIGHT(Rtrim ([FeildName]), 6) AS INT)) + 1 ), 6) AS VARCHAR(6))

FROM   <TableName>

--Note: The use of right is deployed in the query because FeildName contains alphabets before numerics that is why we use to pull the 6 numeric digits form the right and get the max value and add a number and give the padding of 6 numeric with '000000'.