Modeling Time<\/a> article.)<\/p>\nThe advantage of half-open intervals is that they can be concatenated, subsseted and defined in calculus as limits.<\/p>\n
Decades ago, when the SQL standards were being worked on in ANSI X3H2, we defined what is now the SYMMETRIC BETWEEN<\/code> predicate one meeting. Microsoft even put it in their Access database product. At the next meeting, the committee backed out this change. Currently, however, we reverse ourselves again and use a new optional keyword, SYMMETRIC<\/code>. This is implemented in PostgreSQL, but not currently in SQL Server.<\/p>\n<value-1>[ BETWEEN SYMMETRIC<value-2> AND <value-3><\/code><\/p>\nas a shorthand for:<\/p>\n
((<value-1>BETWEEN <value-2> AND <value-3>)<\/code>
\n\u00a0 \u00a0OR <value-1> BETWEEN <value-3> AND <value-2>))<\/code><\/p>\nNotice that we still don\u2019t have the support for open intervals with this operator.<\/p>\n
[NOT] EXIST <subquery><\/h2>\n
The simple EXISTS<\/code>() also, as an interesting history. Technically, it takes a subquery. This means were supposed to have a SELECT FROM<\/code> statement parameter. But the SELECT<\/code> clause is redundant.<\/p>\nThe reason that it\u2019s in syntax is that originally the first attempts at SQL would expand the subquery out completely to test for the existence qualified data. But if you think about it, the minute you get just one row back, even if it includes NULLs<\/code> (yes, NULLs<\/code> exists, even if they don\u2019t have any value), this operator will return TRUE<\/code> in fact, it\u2019s one of the few logical operators in SQL that cannot return an UNKNOWN<\/code>.<\/p>\nPattern Matching<\/h2>\n
The syntax for the original pattern matching predicate is < match value> [NOT] LIKE <pattern expression> [ESCAPE <escape character>]<\/code>. SQL got this version of a general regular expression parser from IBM. Since we never intended the language to be a text handling tool, we picked a simple basic version of grep (general regular expression parser) from UNIX. Later, SQL added \u00a0the SIMILAR TO<\/code> regular expression tool.<\/p>\nThere are several wildcards that can be a part of the pattern expression:<\/p>\n
\n- The percent sign % represents zero, one, or multiple pattern characters.<\/li>\n
- The underscore sign _ represents one, single character. This was really a bad choice are part because a string of underscores is hard to count. Modern word processors and printers tend to make them all into one single line with the resolution that we have had since we stopped using old unit record line printer equipment.<\/li>\n
- Square brackets
[<\/code> and ] <\/code>allow you to specify a range of characters like \u2018[0-9]\u2019. So, any single character that appear in the character set you are using that are between 0 and 9 would match. In this case, any of 0, 1, 2, 3, 4, 5, 6, 7, 8, or 9.<\/li>\n- The
ESCAPE<\/code> clause declares a pattern character with the property that what follows it is to be treated as a literal, not as a pattern. This lets you put underscores and percent signs in strings. This feature does not get used a lot.
\nAs you would expect, a NULL<\/code> in the predicate always returns an UNKNOWN<\/code> result. However, if both pattern and match values are strings of length zero, then the result is TRUE<\/code>.<\/li>\n<\/ul>\nThe LIKE<\/code> operator is not used as much as it should be. You can put it in a CHECK()<\/code> constraint in the DDL and save yourself the trouble of writing code to do a quick validation every time you touch a particular column. My favorite is simply having CHECK (foobar_zipcode LIKE \u2018[0-9][0-9][0-9][0-9][0-9]\u2019)<\/code> on US mailing addresses because it\u2019s cheap and easy.<\/p>\nThe SQL\u201399 standard added the SIMILAR TO<\/code> operator, this is a form of grep()<\/code> based on POSIX, as found in ISO\/IEC 9945 standards. The US government was interested in these standards at the time, so we added it to SQL for portability.<\/p>\nPattern symbols are little more extensive in this regular expression tool. The following are some additional patterns that are part of the standards but not necessarily completely implemented in the RDBMS you are using.<\/p>\n