5

Do you know any reasons why SQL-92 standard has changed glob pattern wildcard characters from * and ? (SQL-89) to % and _?

Currently I need to do mask conversions to allow users searching data with * and ? which they already know from file system. Also, % and _ need escaping more frequently, because they tend to appear in data fields more frequenly than * or ?. What was breaking the glob standard good for?

8

SQL dates back to the early 1970s. It's about the same age as UNIX. UNIX-style file name matching wildcards didn't become ubiquitous until much later.

* was already a reserved character in SQL, as in SELECT * FROM Customer. Having it also be a wildcard would probably have been confusing.
And standardization -- of wildcard characters or anything else -- always takes "prior art" into account, i.e. what database vendors were already doing in the late 1980s / early 1990s. If no one was using * and ? as pattern matching wildcards there's no way those characters would work their way into the standard (the vendors are, after all, represented on the standards committee).

I'd also note that pattern matching (LIKE) expressions aren't exactly analogous to file system globbing. The regular expression functionality found in sed, awk, and many other UNIX utilities IMO represent a much better analogue.
So a better question might be, why didn't SQL adopt regular expressions for pattern matching?

9

Microsoft Access was based on a database engine called Jet which offered * and ? as wildcards for LIKE. These were never a part of the SQL standard. At some point, Microsoft incorporated a SQL-92 mode which offered the standard characters.

The use of % and _ goes back a very long way. My copy of An Introduction to Database Systems by C.J. Date (4th edition, 1986) describes it that way. IBM DB2 (first appeared ca. 1983) had it, and I wouldn't be surprised if predecessors SQL/DS (1981) and System R (mid- to late 1970s) did, too. SQL in those products became the foundation for the standards.

  • FWIW, your first paragraph would be better if you lead with "That was never part of any SQL standard." – DougM Feb 3 '15 at 15:21
  • @DougM - thanks for the note. I can't find for sure whether the change was actually there. This answer was one of sources of my question. – miroxlav Feb 3 '15 at 15:29
  • @miroxlav: Unfortunately, I can't find an online copy of SQL-89, but I have no reason to believe * and ? were in there. At the link in the answer, Microsoft calls them out as "wildcard characters that are specific to the Microsoft Access database engine to use with the Like operator." – Blrfl Feb 3 '15 at 16:00
  • Sure, I don't object. I'm actually not bound to MS Access and the question wasn't inspired by its SQL dialect. It was rather UX and feeling. (You can see come more context in comment below my question.) Let's wait a bit and then conclude the question. – miroxlav Feb 3 '15 at 16:07
  • Too bad I can't mark two answers! Thank you anyway, very appreciated. – miroxlav Feb 3 '15 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.