why should I use _ instead of * wildcard in "like"?

[pnwork]

I noticed that inside of access db I can do ""select '21' like '2*';" and it returns -1. If I do the same in jupyter notebook with sql alchemy, it returns "0". I found out that wildcard becomes underscore _ and I have to modify queries replacing * with _ in jupyter notebook. Why is it so?

[gordthompson]

When Microsoft Access was first being developed over 30 years ago (initial release was 1992), the decision was taken to use * and ? as wildcard characters for the LIKE clause. We can assume that this was at least partly due to the fact that those characters were used by MS-DOS and Windows for pattern matching (e.g., del temp*.* from the DOS prompt), and therefore would be less confusing for Windows users new to working with databases.

When the Access ODBC driver and OLEDB provider were implemented, the decision was taken to use the ANSI standard % and _ characters for LIKE. This was probably a difficult choice, betting on whether it was worse to annoy the "Access people" (by switching to %) or vex the "SQL people" (by continuing to use *). My guess is that the software designers expected relatively few "Access users" to be writing ODBC/OLEDB code for external applications; they would "always" be working within the Access UI itself (or at least within the Microsoft Office ecosystem, which could hide such implementation details from them).

The unfortunate outcome was that LIKE clauses behave differently when run from within the Access UI vs. when used via ODBC/OLEDB. They added the ALIKE (ANSI LIKE) operator to provide a consistent interface (always using % and _), but the LIKE operator still lives on in its original form.

For more details, see this Stack Overflow answer (and comments) here.