Help Texts

Search Syntax

SQLitely supports a simple query syntax for searching the database:

Search for all words this andthis alsothis	Row is matched if each word finds a match in at least one column.
Search for exact word or phrase "do re mi"	Use quotes (") to search for an exact phrase or word. Quoted text is searched exactly as entered, leaving empty space as-is and ignoring any wildcard characters.
Search for either word this OR that	To find results containing at least one of several words, include OR between the words. OR works also for phrases and grouped words (but not keywords).
Group words together (these two) OR this -(none of these)	Surround words with round brackets to group them for OR queries, or for excluding from results.
Search for partially matching text bas*ball	Use an asterisk (*) to make a wildcard query: the wildcard will match any text between its front and rear characters (including empty space and other words).
Exclude words or keywords -notthisword -"not this phrase" -(none of these) -table:notthistable -date:2013	To exclude certain results, add a dash (-) in front of words, phrases, grouped words or keywords.
Search specific tables table:fromthistable view:fromthisview -table:notfromthistable -view:notfromthisview	Use the keyword table:name or view:name to constrain results to specific tables and views only. Search from more than one source by adding more table: or view: keywords, or exclude certain sources by adding a -table: or -view: keyword.
Search specific columns column:fromthiscolumn -column:notfromthiscolumn	Use the keyword column:name to constrain results to specific columns only. Search from more than one column by adding more column: keywords, or exclude certain columns by adding a -column: keyword.
Search from specific time periods date:2008 date:2009-01 date:2005-12-24..2007	To find rows from specific time periods (where source has DATE/DATETIME/TIMESTAMP columns), use the keyword date:period or date:periodstart..periodend. For the latter, either start or end can be omitted. A date period can be year, year-month, or year-month-day. Additionally, date:period can use a wildcard in place of any part, so date:*-12-24 would search for all rows having a timestamp from the 24th of December. Search from a more narrowly defined period by adding more date: keywords.

Examples

• search for "domain.com" in columns where name contains "url":
  domain.com column:url
  
  
• search for "foo bar" from rows where date column values predate 2012:
  "foo bar" date:..2011
  
  
• search for either "John" and "my side" or "Stark" and "your side":
  (john "my side") OR (stark "your side")
  
  
• search for either "birthday" or "cake" from columns where column name contains "message", except where table name contains "log":
  birthday OR cake column:message -table:log
  
  
• search for "TPS report" but not "my TPS report" from rows where date column values are the first day of the month in 2012:
  "tps report" -"my tps report" date:2012-*-1
  
  

All search texts and keywords are case-insensitive by default.
Keywords are global, even when in bracketed (grouped words).
Metadata search supports only table: and view: keywords.