It might seem a bit tricky and you may need to play with this a bit to get it working 100%, but I think you'll get the idea of what I'm trying to do here pretty quickly.

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)

Alternatively, using CLRs and understanding how to write them don't need to go hand in hand. You just need to have permissions on your database to install them.

Here's a pretty nice library called SQL# developed by Solomon Rutzky (SQL Sharp Library of CLRs[/url]) that is really clean to install, is well documented and has the functions in it that you'll need to perform this validity check directly with the RegEx pattern you've provided. RegEx validations will probably be more costly in terms of CPU than doing something like what I provided, but you can always test that assumption (and you should) to be sure.