MS Access SQL：检查字符串中B出现后右侧仅含B字符的查询问题

Let's break down what's wrong with your current query and fix it to meet your requirement: once a 'B' appears, all characters to its right must be 'B' (no other letters or numbers allowed).

The Problem with Your Current Query #

Your existing code checks if the substring starting at the first 'B' does not contain any 'B's (via NOT LIKE '*[B]*') to return "FAIL". That's backwards logic! Instead, you need to check if the substring contains any character that is NOT 'B'—because that's when it should fail.

Your current query:

IIF( MID(Field, INSTR(Field, 'B'), LEN(Field)) NOT LIKE '*[B]*', "FAIL", "PASS" )

This incorrectly returns "PASS" for values like 0000BBBBA because the substring BBBBA does contain 'B's—even though it also has an 'A' which violates your rule.

The Corrected Query #

Instead, we need to check if the post-'B' substring contains any non-'B' characters using the [^B] wildcard (which matches any character that is NOT 'B'). Here's the fix:

IIF( MID(Field, INSTR(Field, 'B')) LIKE '*[^B]*', "FAIL", "PASS" )

Note: We can omit the LEN(Field) parameter in MID() since it defaults to the rest of the string when not specified.

How This Works #

1. INSTR(Field, 'B') finds the position of the first 'B' in your field.
2. MID(Field, INSTR(Field, 'B')) extracts the substring starting at that first 'B' all the way to the end of the string.
3. LIKE '*[^B]*' checks if this substring contains any character that is not 'B':
   • If it does (like the 'A' in 0000BBBBA), the condition is true, so we return "FAIL".
   • If all characters are 'B' (like 0000BBBB), the condition is false, so we return "PASS".

Optional: Handling Fields Without Any 'B's #

If fields that don't contain 'B' at all should be considered valid (which aligns with your requirement, since the rule only applies when 'B' exists), the above query already handles this—INSTR(Field, 'B') returns 0, so MID(Field, 0) returns an empty string, which doesn't match *[^B]*, so it returns "PASS".

If you need to explicitly handle this case (for clarity), you can adjust it to:

IIF( INSTR(Field, 'B') > 0 AND MID(Field, INSTR(Field, 'B')) LIKE '*[^B]*', "FAIL", "PASS" )

内容的提问来源于stack exchange，提问作者Harinie R