Skip to content

Advanced search tips

July 10, 2011

1. Case sensitivity.

When querying using ‘=’ and ‘!=’ the we are looking for an exact match, the match is case sensitive. When running ‘~’ (like) and ‘!~’ (unlike) the match is case insensitive.

2. Using quotes.

In the search syntax white spaces are used as a delimiter. Here are some examples of the way a query will be interpreted:

description ~ “created successfully” => list all notifications that contains “created successfully”

description ~ created successfully => list all notifications that contains “created” and at least one of it’s text fields contains “successfully”

description !~ created successfully => list all notifications that doesn’t contains “created” and at least one of it’s text fields contains “successfully”

In the second and third example “successfully” if an additional term that is interpreted as a ‘free text’ search

3. Wildcard ‘_’, ‘%’ and ‘*’

The search terms are translated to SQL queries. ‘~’ -> LIKE ‘!~’ -> NOT LIKE

LIKE statement supports two wildcards ‘_’ and ‘%’.

 ‘_’ is a wildcard for a single character replacement. For example the search “name ~ fo_” will match both ‘foo’ and ‘for’.

The ‘%’ and ‘*’ wildcard will replace any number of characters including 0. For example the search ‘name ~ corp%’ will match both ‘corp’ and ‘corporation’. The more common ‘*’ wildcard is not an SQL wildcard but can be used in the search.

When the ‘like’ or ‘unlike’ search is processed, a ‘%’ wildcard is added at the beginning and the end of a value if it isn’t there in the first place.

For example: The search ‘name ~ foo’ will become “name LIKE ‘%foo%'” The search ‘name ~ foo%’ will become “name LIKE ‘foo%'”

Advertisements

From → End Users

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: