WITH SampleData (s) AS (
    -- RegEx: ([A-Za-z]{3,6}_?[0-9]{4,7})
    SELECT 'ABVAAA_1234567'                 -- valid
    UNION ALL SELECT 'AASDAAA_123'          -- invalid
    UNION ALL SELECT 'ACd123'               -- valid
    UNION ALL SELECT 'ACD_12345677'         -- invalid
    UNION ALL SELECT '12345677'             -- invalid
    )
SELECT s, PosUnd, PosNum, PosAlph, LEN(s)
    ,IsValid=CASE 
        WHEN PosUnd > 0 AND PosAlph <> 0 AND PosNum - PosUnd = 1 AND PosUnd - PosAlph <= 6 AND LEN(s) - PosNum <= 6
            THEN 1
        WHEN PosUnd = 0 AND PosAlph <> 0 AND PosUnd - PosAlph <= 7 AND LEN(s) - PosNum <= 7
            THEN 1
        --WHEN 
        ELSE 0 END
FROM SampleData
CROSS APPLY (SELECT PATINDEX('%[_]%', s)) a(PosUnd)
CROSS APPLY (SELECT PATINDEX('%[0-9]%', s)) b(PosNum)
CROSS APPLY (SELECT PATINDEX('%[A-Za-z]%', s)) c(PosAlph)