I’m accessing a Microsoft Access database using ODBC.
According to the w3schools SQL tutorial, the ANSI-92 wildcard for “any character not in brackets” should be ^. However, their own example uses the ANSI-89 wildcard !.
The MSDN documentation is also confusing. The ANSI-92 section shows ^ character, but the example next to it actually uses !. That looks like an error and I’ve filled out feedback to notify Microsoft.
What is the correct “any character not in brackets” syntax? And will this apply across multiple databases and access technologies (DAO, ODBC, OleDB, MySQL, etc)?
Advertisement
Answer
There is no way to do this across databases. SQL supports the LIKE syntax that has exactly two wildcards:
%matches zero or more characters._matches exactly one character.
One could throw in the character to escape wildcards as well.
In addition, SQL Server supports character classes as explained in the tutorial (which seems to be accurate). The only other database that comes to mind that does this is Sybase (which has the same code base). MS Access just has a bastardized version with special characters. It never supported SQL standards in this respect.
Most other databases implement full regular expression support (YAY!), but their syntax varies by database. So, there is not a database-independent way to do what you want.