Filtering for alphanumeric pattern with variable length

Question

I would like to find invalid IBAN codes in a column "iban"

The requirements for a valid IBAN in my case are:

1. Min length of 16 and max length of 31
2. Starts with two strictly alphabetical characters A-Z
3. The next two positions (3 and 4) are strictly numerical 0-9
4. The rest of the string must be aplhanumerical A-Z 0-9, lower or upper case doesn't matter

Points 1) to 3) are no problem but I don't know about the last one because it does not have a fixed length.

I'm working with SQL Server.

Thus far I have this query:

SELECT id, iban
FROM foo
WHERE NOT (
     LEN(iban) BETWEEN 16 AND 31
     AND iban LIKE '[A-Z][A-Z][0-9][0-9]%'
          )

Answer 1

You may add a second LIKE expression which asserts that entire IBAN code is alphanumeric:

SELECT id, iban
FROM foo
WHERE NOT (
    LEN(iban) BETWEEN 16 AND 31 AND        -- length 16-31
    iban LIKE '[A-Z][A-Z][0-9][0-9]%' AND  -- e.g. starts with AB12
    iban NOT LIKE '%[^A-Za-z0-9]%'         -- only alphanumeric
);