SELECT DISTINCT 
  (SELECT MAX(FinNumber) FROM taClients AS B WHERE (LEFT(B.FinNumber, 3) = LEFT(A.FinNumber, 3))) AS @pMainCharacter@pSubCharacterMaxNum
FROM 
  taClients AS A
WHERE
  (MID(A.FinNumber, 1, 1) = @aMainCharacter) AND
  (MID(A.FinNumber, 2, 1) = @aSubCharacter) AND
  (MID(A.FinNumber, 1, 1) >= 'A') AND (MID(A.FinNumber, 1, 1) <= 'Z') AND
  (MID(A.FinNumber, 2, 1) >= 'A') AND (MID(A.FinNumber, 2, 1) <= 'Z') AND
  (MID(A.FinNumber, 3, 1) >= 'A') AND (MID(A.FinNumber, 3, 1) <= 'Z') 
  