Informix SQL substring in the where clause

Select a few lines with WHERE

  • To search for whole numbers, these are noted directly. For numbers with decimal places, the point is the valid separator (A_Preis> 99.99). Text constants are set in single quotation marks ('). If you want to search for a single apostrophe, it is sufficient to double it: WHERE V.V_Name = 'O''Neil' finds O'Neil
  • Basically, every comparison with an operator (NOT, =, <,>, IN) is evaluated to NULL if one of the two expressions is NULL. The negation of such an expression therefore also yields NULL. This means that an entire set of rows with NULL cells is not completely exhausted by a comparison and its negative.
  • WHERE A.A_PREIS = 10.00 ... UNION ... WHERE A.A_PREIS <> 10.00 delivers all lines with price, but not those articles for which no price has been defined and for which the A_PREIS cell is empty. Because with an empty cell, none of the above conditions can be tested positively.
  • An example with a logical operator: SELECT A. * FROM ARTICLE AS A WHERE A.A_NAME = 'Hose' UNION SELECT A. * FROM ARTICLE AS A WHERE NOT A.A_NAME = 'Hose' As part of every logic course you will communicated that this query returns all lines, in this case it outputs 4 lines. Then delete a cell in the A_NAME column as a test, e.g. a cell with the value 'Shirt'. Delete only the cell, not the row. If you then run the query again, you will only get three rows of results.
  • Operator order: A comparison with = binds the most, so that an expression A_NAME = 11 Or A_PREIS> 100 is interpreted as desired: (A_NAME = 11) Or (A_PREIS> 100) Use different logical operators in a WHERE expression, so use You best of brackets. For anyone who later edits the code and does not know the exact order of the operators, this is the safest solution to prevent unintentional errors from creeping in. Consider the following expression: WHERE A.A_NR BETWEEN 11 AND 10 OR 15 The first expression is fulfilled for A_NR = 11 at least for Access in Sql-Interactive-Learning, so that one data record would have to be expected. Since AND is stronger than OR and AND belongs to BETWEEN, the expression is actually evaluated as follows: WHERE (A.A_NR BETWEEN 11 AND 10) OR (15) The expression (15) is different from 0, so that in most database Systems true. So it is a constant so that all lines are printed.
  • Wildcard search in Access: If queries are created in Access using the usual programming environment, then, contrary to the above-mentioned conventions, the '?' Must be used for searching for a single character, and '*' for searching for multiple characters. In this case, the DAO (Data Access Objects) model is used, which implements a non-standard Sql dialect. If, on the other hand, an Access database is accessed via one of the programming interfaces ADO or .NET, the standard characters '_' and '%' must be used. Sql interactive learning uses .NET access techniques, so that the standard is to be used here.
  • Instead of an expression in the form = , either another column name or a separate select query in brackets can be specified on the right. This must return exactly one cell. Likewise, for an expression of the form IN (), a subquery can be used that returns exactly one column. See the section on subqueries for examples.
  • EXISTS can, for example, save resources on the sql server. can be used at the beginning of a script to check whether an object exists and to delete it if necessary: ​​If (Exists (Select A.Table_Name From Information_Schema.Tables As A Where A.Table_Name = 'Article')) Drop Table Article This technique is preferable to any Group By or Count (*), such as the following code: If ((Select Count (*) From Information_Schema.Tables As A Where A.Table_Name = 'Article')> 0) Drop Table Article