Search Syntax and Operators

There are basically two different ways to use the Data Search feature.

First way:

A 'fuzzy' search (using the LIKE operator and with %-wildcards attached both before and after the search string you entered). This is basically a complete Google search logic adopted to database search. It will transparently for user translate the Google search syntax to SQL and fire the resulting SQL-statements against the database objects selected for search.

'jo' entered the search will find both 'Joe', 'Joseph', and 'navajo'. You may use various operators to refine the search. First of all note that the SPACE character in the search string denotes a logical AND on the words on either side of the SPACE.

So entering snow leopard and snow AND leopard is exactly the same. In both cases the WHERE-clause will be

.. WHERE column LIKE '%snow%' and column LIKE '%leopard%'

You may specify the OR operator instead entering 'snow OR leopard' and the WHERE clause will be

.. WHERE column LIKE '%snow%' OR column LIKE '%leopard%'

Synonyms for AND-operator is "&" and SPACE character (as described above). Synonym for OR-operator is "|". AND and OR are considered operators only when there is whitespace on both sides. 'hand foot' is the same as 'hand AND foot' - not 'h AND foot'. Note that SQL-wildcards ('%' and '_') in the search string will be treated as literals and not as wildcards.

Also the "double quote" operator is supported in order to avoid the SPACE-character becoming a synonym for the AND-operator. Entering the search string "snow leopard" (with quotes) will generate the WHERE-clause

.. WHERE column LIKE '%snow leopard%'

Note that the single-quote character cannot be used as a synonym for the double-quote character. The single-quote character has no special meaning in SQLyog Data Search. It is just a plain character like 'a' or '1' is. Also you cannot use paranthesis'es for 'nesting' AND and OR operators. The expression is evaluated from left to right.

A few special considerations that most users will probably not care about are: If a "double quoted string" contains a double quote-character itself followed by whitespace this 'inner' double quote-character must be escaped with the \-character (backslash). To search for a string that is also an operator use double quotes. so to find instances of '&' and '|' stored in the database enter the search string "&" OR "|" (with quotes).

Also the '+' and '-' operators are supported. +string or +"some string" will not append a '%'-wildcard to the ends of the specified search string. So '+' is an exact match with the search string you specified. For instance +mysql will find rows where a column exactly matches 'mysql' - and not 'mysql server' for instance. '-' behaves like '+' except that the LIKE clause will be replaced with a NOT LIKE clause. Note that '+' and '-' only work as operators when they have no whitespace to their right. +lion will find exact matches on 'lion' whilst + lion will find rows where there are fuzzy matches on BOTH 'lion' AND '+' in same columns of the database objects searched.

When entering NULL as the search string the 'IS NULL' SQL-operator is used. Example: enter "snow leopard" OR +"bengali tiger" OR NULL (with quotes) and the where clause will be

.. WHERE column LIKE '%snow leopard%' OR column LIKE 'bengali tiger' or column IS NULL

When combining '+' and '-' operators with NULL, +NULL is exactly the same as NULL and -NULL will generate the SQL-operator IS NOT NULL. To search for the literal string 'NULL' should you want to, `backquote` it as `NULL` or enclose it in double quotes as "NULL".

Finally you can use hexadecimal patterns for search writing the hexadecimal search string in x'..' notation. A valid HEX string with characters in the range 0..F (where the range A..F is case insensitive) will need to be specified. If for instance you specify x'STU' (where 'STU' is not a valid HEX-pattern) as the search string we will simply find fuzzy matches on the literal string " x'STU' ". Also hexadecimal search can be combined with '+' and '-' operators for 'exact match' and 'no match' (but the "double quote" operator is not supported with binary search). Search with hexadecimal strings require MySQL 4.1 or higher. With older server versions the expression is treated like a literal.

Second way:

A search based on a regular expression. It will use the REGEXP operator with the string you enter as operand. If for instance you want to find all occurrences of 'Jim' and 'Jimi' but not 'Jimmy', 'Jimmie' etc. using the option to use regular expression enter '^jim$|^jimi$' (without quotes). The WHERE clause generated will be like WHERE column REGEXP '^jim$|^jimi$'; . Use of regular expressions is a powerful method for string matching and is worth studying and learning - and often preferred by database administrators.

Both search methods are case insensitive when searching in strings except for data stored with a binary or case sensitive collation or stored in binary datatypes (including BLOB). However note the exception that when searching strings containing non-ASCII charactes with regular expressions the search is case sensitive.

Also all operators and the NULL keyword are case insensitive.

Once the search has been completed all matches are displayed in a page-wise manner much like a web page. Each occurrence of the search string found is a click-able link. By clicking the link, the lower half of the tab (that was 'greyed out' till now) will populate a data GRID identical to the one in the DATA and RESULT tabs. The table where this particular occurrence was found will display here and the same occurrence that you clicked on in the search result will be selected. Each page lists out twenty of the search results. You can perform all kinds of operations on data here in the same way as you do when working with data in DATA and RESULT tabs.

Note: This feature is available in SQLyog Ultimate.