ms access sql Check if string contains only valid characters-CodePudding

I have a field which should not have any other letters/digits other than B to its right once B occurred. i.e. valid value : 0000BBBB Invalid value : 0000BAG0

So far, I have succeeded to substring from B's occurrence. But I'm stuck at how to check if each character is B.

Below is the query I have written so far:

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

But this results in pass for 0000BBBBA which is actually Fail.

CodePudding user response：

The character class [!B] matches any character other than the letter B. So take the substring you get from Mid() and check whether it is Not Like '*[!B]*' --- IOW whether it contains any character other than B.

SELECT
    t.Field,
    IIf(Mid(t.Field, InStr(t.Field, 'B')) Not Like '*[!B]*', 'PASS', 'FAIL')
FROM
FROM YourTable AS t