PostgreSQL: Not fully alphanumeric, Not fully in capital letters, Length different than 18

Question

could you please help me writing the script which would bring an output for these requirements??

Select IBANs which are:

• Not fully alphanumeric
• Not fully in capital letters
• Length different than 18

My current query:

Select *
FROM Table
WHERE ibanaccount NOT LIKE '%[A-Z0-9]%' 
AND length(ibanaccount)<>18 
AND UPPER(ibanaccount) = 'AAbbCC';

Answer 1

I would use a regular expressions here:

SELECT *
FROM yourTable
WHERE ibnaccount ~* '[^A-Z0-9]' AND  -- matches non alphanumeric character
      LENGTH(ibanaccount) <> 18 AND  -- length is not 18
      ibnaccount ~ '[^A-Z]';         -- at least one non uppercase letter