/ Hex Artifact Content
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Artifact a19711dc5458c20734b8e485e75fb1981ec2427a:


0000: 0a 31 2e 20 4f 56 45 52 56 49 45 57 0a 0a 20 20  .1. OVERVIEW..  
0010: 54 68 69 73 20 52 45 41 44 4d 45 20 66 69 6c 65  This README file
0020: 20 64 65 73 63 72 69 62 65 73 20 74 68 65 20 73   describes the s
0030: 79 6e 74 61 78 20 6f 66 20 74 68 65 20 61 72 67  yntax of the arg
0040: 75 6d 65 6e 74 73 20 74 68 61 74 20 6d 61 79 20  uments that may 
0050: 62 65 20 70 61 73 73 65 64 20 74 6f 0a 20 20 74  be passed to.  t
0060: 68 65 20 46 54 53 33 20 4d 41 54 43 48 20 6f 70  he FTS3 MATCH op
0070: 65 72 61 74 6f 72 20 75 73 65 64 20 66 6f 72 20  erator used for 
0080: 66 75 6c 6c 2d 74 65 78 74 20 71 75 65 72 69 65  full-text querie
0090: 73 2e 20 46 6f 72 20 65 78 61 6d 70 6c 65 2c 20  s. For example, 
00a0: 69 66 20 74 61 62 6c 65 20 0a 20 20 22 74 31 22  if table .  "t1"
00b0: 20 69 73 20 61 6e 20 46 74 73 33 20 76 69 72 74   is an Fts3 virt
00c0: 75 61 6c 20 74 61 62 6c 65 2c 20 74 68 65 20 66  ual table, the f
00d0: 6f 6c 6c 6f 77 69 6e 67 20 53 51 4c 20 71 75 65  ollowing SQL que
00e0: 72 79 3a 0a 0a 20 20 20 20 53 45 4c 45 43 54 20  ry:..    SELECT 
00f0: 2a 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20  * FROM t1 WHERE 
0100: 3c 63 6f 6c 3e 20 4d 41 54 43 48 20 3c 66 75 6c  <col> MATCH <ful
0110: 6c 2d 74 65 78 74 20 71 75 65 72 79 3e 0a 0a 20  l-text query>.. 
0120: 20 6d 61 79 20 62 65 20 75 73 65 64 20 74 6f 20   may be used to 
0130: 72 65 74 72 69 65 76 65 20 61 6c 6c 20 72 6f 77  retrieve all row
0140: 73 20 74 68 61 74 20 6d 61 74 63 68 20 61 20 73  s that match a s
0150: 70 65 63 69 66 69 65 64 20 66 6f 72 20 66 75 6c  pecified for ful
0160: 6c 2d 74 65 78 74 20 71 75 65 72 79 2e 20 0a 20  l-text query. . 
0170: 20 54 68 65 20 74 65 78 74 20 22 3c 63 6f 6c 3e   The text "<col>
0180: 22 20 73 68 6f 75 6c 64 20 62 65 20 72 65 70 6c  " should be repl
0190: 61 63 65 64 20 62 79 20 65 69 74 68 65 72 20 74  aced by either t
01a0: 68 65 20 6e 61 6d 65 20 6f 66 20 74 68 65 20 66  he name of the f
01b0: 74 73 33 20 74 61 62 6c 65 20 0a 20 20 28 69 6e  ts3 table .  (in
01c0: 20 74 68 69 73 20 63 61 73 65 20 22 74 31 22 29   this case "t1")
01d0: 2c 20 6f 72 20 62 79 20 74 68 65 20 6e 61 6d 65  , or by the name
01e0: 20 6f 66 20 6f 6e 65 20 6f 66 20 74 68 65 20 63   of one of the c
01f0: 6f 6c 75 6d 6e 73 20 6f 66 20 74 68 65 20 66 74  olumns of the ft
0200: 73 33 20 0a 20 20 74 61 62 6c 65 2e 20 3c 66 75  s3 .  table. <fu
0210: 6c 6c 2d 74 65 78 74 2d 71 75 65 72 79 3e 20 73  ll-text-query> s
0220: 68 6f 75 6c 64 20 62 65 20 72 65 70 6c 61 63 65  hould be replace
0230: 64 20 62 79 20 61 6e 20 53 51 4c 20 65 78 70 72  d by an SQL expr
0240: 65 73 73 69 6f 6e 20 74 68 61 74 20 0a 20 20 63  ession that .  c
0250: 6f 6d 70 75 74 65 73 20 74 6f 20 61 20 73 74 72  omputes to a str
0260: 69 6e 67 20 63 6f 6e 74 61 69 6e 69 6e 67 20 61  ing containing a
0270: 6e 20 46 74 73 33 20 71 75 65 72 79 2e 0a 0a 20  n Fts3 query... 
0280: 20 49 66 20 74 68 65 20 6c 65 66 74 2d 68 61 6e   If the left-han
0290: 64 2d 73 69 64 65 20 6f 66 20 74 68 65 20 4d 41  d-side of the MA
02a0: 54 43 48 20 6f 70 65 72 61 74 6f 72 20 69 73 20  TCH operator is 
02b0: 73 65 74 20 74 6f 20 74 68 65 20 6e 61 6d 65 20  set to the name 
02c0: 6f 66 20 74 68 65 0a 20 20 66 74 73 33 20 74 61  of the.  fts3 ta
02d0: 62 6c 65 2c 20 74 68 65 6e 20 62 79 20 64 65 66  ble, then by def
02e0: 61 75 6c 74 20 74 68 65 20 71 75 65 72 79 20 6d  ault the query m
02f0: 61 79 20 62 65 20 6d 61 74 63 68 65 64 20 61 67  ay be matched ag
0300: 61 69 6e 73 74 20 61 6e 79 20 63 6f 6c 75 6d 6e  ainst any column
0310: 0a 20 20 6f 66 20 74 68 65 20 74 61 62 6c 65 2e  .  of the table.
0320: 20 49 66 20 69 74 20 69 73 20 73 65 74 20 74 6f   If it is set to
0330: 20 61 20 63 6f 6c 75 6d 6e 20 6e 61 6d 65 2c 20   a column name, 
0340: 74 68 65 6e 20 62 79 20 64 65 66 61 75 6c 74 20  then by default 
0350: 74 68 65 20 71 75 65 72 79 0a 20 20 6d 61 79 20  the query.  may 
0360: 6f 6e 6c 79 20 6d 61 74 63 68 20 74 68 65 20 73  only match the s
0370: 70 65 63 69 66 69 65 64 20 63 6f 6c 75 6d 6e 2e  pecified column.
0380: 20 49 6e 20 62 6f 74 68 20 63 61 73 65 73 20 74   In both cases t
0390: 68 69 73 20 6d 61 79 20 62 65 20 6f 76 65 72 72  his may be overr
03a0: 69 64 65 6e 0a 20 20 61 73 20 70 61 72 74 20 6f  iden.  as part o
03b0: 66 20 74 68 65 20 71 75 65 72 79 20 74 65 78 74  f the query text
03c0: 20 28 73 65 65 20 73 65 63 74 69 6f 6e 73 20 32   (see sections 2
03d0: 20 61 6e 64 20 33 20 62 65 6c 6f 77 29 2e 0a 0a   and 3 below)...
03e0: 20 20 41 73 20 6f 66 20 53 51 4c 69 74 65 20 76    As of SQLite v
03f0: 65 72 73 69 6f 6e 20 33 2e 36 2e 38 2c 20 46 74  ersion 3.6.8, Ft
0400: 73 33 20 73 75 70 70 6f 72 74 73 20 74 77 6f 20  s3 supports two 
0410: 73 6c 69 67 68 74 6c 79 20 64 69 66 66 65 72 65  slightly differe
0420: 6e 74 20 71 75 65 72 79 20 0a 20 20 66 6f 72 6d  nt query .  form
0430: 61 74 73 3b 20 74 68 65 20 73 74 61 6e 64 61 72  ats; the standar
0440: 64 20 73 79 6e 74 61 78 2c 20 77 68 69 63 68 20  d syntax, which 
0450: 69 73 20 75 73 65 64 20 62 79 20 64 65 66 61 75  is used by defau
0460: 6c 74 2c 20 61 6e 64 20 74 68 65 20 65 6e 68 61  lt, and the enha
0470: 6e 63 65 64 0a 20 20 71 75 65 72 79 20 73 79 6e  nced.  query syn
0480: 74 61 78 20 77 68 69 63 68 20 63 61 6e 20 62 65  tax which can be
0490: 20 73 65 6c 65 63 74 65 64 20 62 79 20 63 6f 6d   selected by com
04a0: 70 69 6c 69 6e 67 20 77 69 74 68 20 74 68 65 20  piling with the 
04b0: 70 72 65 2d 70 72 6f 63 65 73 73 6f 72 0a 20 20  pre-processor.  
04c0: 73 79 6d 62 6f 6c 20 53 51 4c 49 54 45 5f 45 4e  symbol SQLITE_EN
04d0: 41 42 4c 45 5f 46 54 53 33 5f 50 41 52 45 4e 54  ABLE_FTS3_PARENT
04e0: 48 45 53 49 53 20 64 65 66 69 6e 65 64 2e 0a 0a  HESIS defined...
04f0: 20 20 20 20 2d 44 53 51 4c 49 54 45 5f 45 4e 41      -DSQLITE_ENA
0500: 42 4c 45 5f 46 54 53 33 5f 50 41 52 45 4e 54 48  BLE_FTS3_PARENTH
0510: 45 53 49 53 0a 0a 32 2e 20 53 54 41 4e 44 41 52  ESIS..2. STANDAR
0520: 44 20 51 55 45 52 59 20 53 59 4e 54 41 58 0a 0a  D QUERY SYNTAX..
0530: 20 20 57 68 65 6e 20 75 73 69 6e 67 20 74 68 65    When using the
0540: 20 73 74 61 6e 64 61 72 64 20 46 74 73 33 20 71   standard Fts3 q
0550: 75 65 72 79 20 73 79 6e 74 61 78 2c 20 61 20 71  uery syntax, a q
0560: 75 65 72 79 20 75 73 75 61 6c 6c 79 20 63 6f 6e  uery usually con
0570: 73 69 73 74 73 20 6f 66 20 61 20 0a 20 20 6c 69  sists of a .  li
0580: 73 74 20 6f 66 20 74 65 72 6d 73 20 28 77 6f 72  st of terms (wor
0590: 64 73 29 20 73 65 70 61 72 61 74 65 64 20 62 79  ds) separated by
05a0: 20 77 68 69 74 65 2d 73 70 61 63 65 20 63 68 61   white-space cha
05b0: 72 61 63 74 65 72 73 2e 20 54 6f 20 6d 61 74 63  racters. To matc
05c0: 68 20 61 0a 20 20 71 75 65 72 79 2c 20 61 20 72  h a.  query, a r
05d0: 6f 77 20 28 6f 72 20 63 6f 6c 75 6d 6e 29 20 6f  ow (or column) o
05e0: 66 20 61 6e 20 46 74 73 33 20 74 61 62 6c 65 20  f an Fts3 table 
05f0: 6d 75 73 74 20 63 6f 6e 74 61 69 6e 20 65 61 63  must contain eac
0600: 68 20 6f 66 20 74 68 65 20 73 70 65 63 69 66 69  h of the specifi
0610: 65 64 0a 20 20 74 65 72 6d 73 2e 20 46 6f 72 20  ed.  terms. For 
0620: 65 78 61 6d 70 6c 65 2c 20 74 68 65 20 66 6f 6c  example, the fol
0630: 6c 6f 77 69 6e 67 20 71 75 65 72 79 3a 0a 0a 20  lowing query:.. 
0640: 20 20 20 3c 63 6f 6c 3e 20 4d 41 54 43 48 20 27     <col> MATCH '
0650: 68 65 6c 6c 6f 20 77 6f 72 6c 64 27 0a 0a 20 20  hello world'..  
0660: 6d 61 74 63 68 65 73 20 72 6f 77 73 20 28 6f 72  matches rows (or
0670: 20 63 6f 6c 75 6d 6e 73 2c 20 69 66 20 3c 63 6f   columns, if <co
0680: 6c 3e 20 69 73 20 74 68 65 20 6e 61 6d 65 20 6f  l> is the name o
0690: 66 20 61 20 63 6f 6c 75 6d 6e 20 6e 61 6d 65 29  f a column name)
06a0: 20 74 68 61 74 20 0a 20 20 63 6f 6e 74 61 69 6e   that .  contain
06b0: 20 61 74 20 6c 65 61 73 74 20 6f 6e 65 20 69 6e   at least one in
06c0: 73 74 61 6e 63 65 20 6f 66 20 74 68 65 20 74 6f  stance of the to
06d0: 6b 65 6e 20 22 68 65 6c 6c 6f 22 2c 20 61 6e 64  ken "hello", and
06e0: 20 61 74 20 6c 65 61 73 74 20 6f 6e 65 20 0a 20   at least one . 
06f0: 20 69 6e 73 74 61 6e 63 65 20 6f 66 20 74 68 65   instance of the
0700: 20 74 6f 6b 65 6e 20 22 77 6f 72 6c 64 22 2e 20   token "world". 
0710: 54 6f 6b 65 6e 73 20 6d 61 79 20 62 65 20 67 72  Tokens may be gr
0720: 6f 75 70 65 64 20 69 6e 74 6f 20 70 68 72 61 73  ouped into phras
0730: 65 73 20 75 73 69 6e 67 0a 20 20 71 75 6f 74 61  es using.  quota
0740: 74 69 6f 6e 20 6d 61 72 6b 73 2e 20 49 6e 20 74  tion marks. In t
0750: 68 69 73 20 63 61 73 65 2c 20 61 20 6d 61 74 63  his case, a matc
0760: 68 69 6e 67 20 72 6f 77 20 6f 72 20 63 6f 6c 75  hing row or colu
0770: 6d 6e 20 6d 75 73 74 20 63 6f 6e 74 61 69 6e 20  mn must contain 
0780: 65 61 63 68 0a 20 20 6f 66 20 74 68 65 20 74 6f  each.  of the to
0790: 6b 65 6e 73 20 69 6e 20 74 68 65 20 70 68 72 61  kens in the phra
07a0: 73 65 20 69 6e 20 74 68 65 20 6f 72 64 65 72 20  se in the order 
07b0: 73 70 65 63 69 66 69 65 64 2c 20 77 69 74 68 20  specified, with 
07c0: 6e 6f 20 69 6e 74 65 72 76 65 6e 69 6e 67 0a 20  no intervening. 
07d0: 20 74 6f 6b 65 6e 73 2e 20 46 6f 72 20 65 78 61   tokens. For exa
07e0: 6d 70 6c 65 2c 20 74 68 65 20 71 75 65 72 79 3a  mple, the query:
07f0: 0a 0a 20 20 20 20 3c 63 6f 6c 3e 20 4d 41 54 43  ..    <col> MATC
0800: 48 20 27 22 68 65 6c 6c 6f 20 77 6f 72 6c 64 22  H '"hello world"
0810: 20 6a 6f 65 22 0a 0a 20 20 6d 61 74 63 68 65 73   joe"..  matches
0820: 20 74 68 65 20 66 69 72 73 74 20 6f 66 20 74 68   the first of th
0830: 65 20 66 6f 6c 6c 6f 77 69 6e 67 20 74 77 6f 20  e following two 
0840: 64 6f 63 75 6d 65 6e 74 73 2c 20 62 75 74 20 6e  documents, but n
0850: 6f 74 20 74 68 65 20 73 65 63 6f 6e 64 20 6f 72  ot the second or
0860: 0a 20 20 74 68 69 72 64 3a 0a 0a 20 20 20 20 22  .  third:..    "
0870: 27 48 65 6c 6c 6f 20 77 6f 72 6c 64 27 2c 20 73  'Hello world', s
0880: 61 69 64 20 4a 6f 65 2e 22 0a 20 20 20 20 22 4f  aid Joe.".    "O
0890: 6e 65 20 73 68 6f 75 6c 64 20 61 6c 77 61 79 73  ne should always
08a0: 20 67 72 65 65 74 20 74 68 65 20 77 6f 72 6c 64   greet the world
08b0: 20 77 69 74 68 20 61 20 63 68 65 65 72 79 20 68   with a cheery h
08c0: 65 6c 6c 6f 2c 20 74 68 6f 75 67 68 74 20 4a 6f  ello, thought Jo
08d0: 65 2e 22 0a 20 20 20 20 22 48 6f 77 20 6d 61 6e  e.".    "How man
08e0: 79 20 68 65 6c 6c 6f 20 77 6f 72 6c 64 20 70 72  y hello world pr
08f0: 6f 67 72 61 6d 73 20 63 6f 75 6c 64 20 74 68 65  ograms could the
0900: 69 72 20 62 65 3f 22 0a 0a 20 20 41 73 20 77 65  ir be?"..  As we
0910: 6c 6c 20 61 73 20 67 72 6f 75 70 69 6e 67 20 74  ll as grouping t
0920: 6f 6b 65 6e 73 20 74 6f 67 65 74 68 65 72 20 62  okens together b
0930: 79 20 70 68 72 61 73 65 2c 20 74 68 65 20 62 69  y phrase, the bi
0940: 6e 61 72 79 20 4e 45 41 52 20 6f 70 65 72 61 74  nary NEAR operat
0950: 6f 72 20 0a 20 20 6d 61 79 20 62 65 20 75 73 65  or .  may be use
0960: 64 20 74 6f 20 73 65 61 72 63 68 20 66 6f 72 20  d to search for 
0970: 72 6f 77 73 20 74 68 61 74 20 63 6f 6e 74 61 69  rows that contai
0980: 6e 20 74 77 6f 20 6f 72 20 6d 6f 72 65 20 73 70  n two or more sp
0990: 65 63 69 66 69 65 64 20 74 6f 6b 65 6e 73 20 0a  ecified tokens .
09a0: 20 20 6f 72 20 70 68 72 61 73 65 73 20 77 69 74    or phrases wit
09b0: 68 69 6e 20 61 20 73 70 65 63 69 66 69 65 64 20  hin a specified 
09c0: 70 72 6f 78 69 6d 69 74 79 20 6f 66 20 65 61 63  proximity of eac
09d0: 68 20 6f 74 68 65 72 2e 20 54 68 65 20 4e 45 41  h other. The NEA
09e0: 52 20 6f 70 65 72 61 74 6f 72 0a 20 20 6d 75 73  R operator.  mus
09f0: 74 20 61 6c 77 61 79 73 20 62 65 20 73 70 65 63  t always be spec
0a00: 69 66 69 65 64 20 69 6e 20 75 70 70 65 72 20 63  ified in upper c
0a10: 61 73 65 2e 20 54 68 65 20 77 6f 72 64 20 22 6e  ase. The word "n
0a20: 65 61 72 22 20 69 6e 20 6c 6f 77 65 72 20 6f 72  ear" in lower or
0a30: 20 6d 69 78 65 64 0a 20 20 63 61 73 65 20 69 73   mixed.  case is
0a40: 20 74 72 65 61 74 65 64 20 61 73 20 61 6e 20 6f   treated as an o
0a50: 72 64 69 6e 61 72 79 20 74 6f 6b 65 6e 2e 20 46  rdinary token. F
0a60: 6f 72 20 65 78 61 6d 70 6c 65 2c 20 74 68 65 20  or example, the 
0a70: 66 6f 6c 6c 6f 77 69 6e 67 20 71 75 65 72 79 3a  following query:
0a80: 0a 0a 20 20 20 20 3c 63 6f 6c 3e 20 4d 41 54 43  ..    <col> MATC
0a90: 48 20 27 65 6e 67 69 6e 65 65 72 69 6e 67 20 4e  H 'engineering N
0aa0: 45 41 52 20 63 6f 6e 73 75 6c 74 61 6e 63 79 27  EAR consultancy'
0ab0: 0a 0a 20 20 6d 61 74 63 68 65 73 20 72 6f 77 73  ..  matches rows
0ac0: 20 74 68 61 74 20 63 6f 6e 74 61 69 6e 20 62 6f   that contain bo
0ad0: 74 68 20 74 68 65 20 22 65 6e 67 69 6e 65 65 72  th the "engineer
0ae0: 69 6e 67 22 20 61 6e 64 20 22 63 6f 6e 73 75 6c  ing" and "consul
0af0: 74 61 6e 63 79 22 20 74 6f 6b 65 6e 73 0a 20 20  tancy" tokens.  
0b00: 69 6e 20 74 68 65 20 73 61 6d 65 20 63 6f 6c 75  in the same colu
0b10: 6d 6e 20 77 69 74 68 20 6e 6f 74 20 6d 6f 72 65  mn with not more
0b20: 20 74 68 61 6e 20 31 30 20 6f 74 68 65 72 20 77   than 10 other w
0b30: 6f 72 64 73 20 62 65 74 77 65 65 6e 20 74 68 65  ords between the
0b40: 6d 2e 20 49 74 20 64 6f 65 73 0a 20 20 6e 6f 74  m. It does.  not
0b50: 20 6d 61 74 74 65 72 20 77 68 69 63 68 20 6f 66   matter which of
0b60: 20 74 68 65 20 74 77 6f 20 74 65 72 6d 73 20 6f   the two terms o
0b70: 63 63 75 72 73 20 66 69 72 73 74 20 69 6e 20 74  ccurs first in t
0b80: 68 65 20 64 6f 63 75 6d 65 6e 74 2c 20 6f 6e 6c  he document, onl
0b90: 79 20 74 68 61 74 0a 20 20 74 68 65 79 20 62 65  y that.  they be
0ba0: 20 73 65 70 65 72 61 74 65 64 20 62 79 20 6f 6e   seperated by on
0bb0: 6c 79 20 31 30 20 74 6f 6b 65 6e 73 20 6f 72 20  ly 10 tokens or 
0bc0: 6c 65 73 73 2e 20 54 68 65 20 75 73 65 72 20 6d  less. The user m
0bd0: 61 79 20 61 6c 73 6f 20 73 70 65 63 69 66 79 0a  ay also specify.
0be0: 20 20 61 20 64 69 66 66 65 72 65 6e 74 20 72 65    a different re
0bf0: 71 75 69 72 65 64 20 70 72 6f 78 69 6d 69 74 79  quired proximity
0c00: 20 62 79 20 61 64 64 69 6e 67 20 22 2f 4e 22 20   by adding "/N" 
0c10: 69 6d 6d 65 64 69 61 74 65 6c 79 20 61 66 74 65  immediately afte
0c20: 72 20 74 68 65 20 4e 45 41 52 0a 20 20 6f 70 65  r the NEAR.  ope
0c30: 72 61 74 6f 72 2c 20 77 68 65 72 65 20 4e 20 69  rator, where N i
0c40: 73 20 61 6e 20 69 6e 74 65 67 65 72 2e 20 46 6f  s an integer. Fo
0c50: 72 20 65 78 61 6d 70 6c 65 3a 0a 0a 20 20 20 20  r example:..    
0c60: 3c 63 6f 6c 3e 20 4d 41 54 43 48 20 27 65 6e 67  <col> MATCH 'eng
0c70: 69 6e 65 65 72 69 6e 67 20 4e 45 41 52 2f 35 20  ineering NEAR/5 
0c80: 63 6f 6e 73 75 6c 74 61 6e 63 79 27 0a 0a 20 20  consultancy'..  
0c90: 73 65 61 72 63 68 65 73 20 66 6f 72 20 61 20 72  searches for a r
0ca0: 6f 77 20 63 6f 6e 74 61 69 6e 69 6e 67 20 61 6e  ow containing an
0cb0: 20 69 6e 73 74 61 6e 63 65 20 6f 66 20 65 61 63   instance of eac
0cc0: 68 20 73 70 65 63 69 66 69 65 64 20 74 6f 6b 65  h specified toke
0cd0: 6e 20 73 65 70 65 72 61 74 65 64 0a 20 20 62 79  n seperated.  by
0ce0: 20 6e 6f 74 20 6d 6f 72 65 20 74 68 61 6e 20 35   not more than 5
0cf0: 20 6f 74 68 65 72 20 74 6f 6b 65 6e 73 2e 20 4d   other tokens. M
0d00: 6f 72 65 20 74 68 61 6e 20 6f 6e 65 20 4e 45 41  ore than one NEA
0d10: 52 20 6f 70 65 72 61 74 6f 72 20 63 61 6e 20 62  R operator can b
0d20: 65 20 75 73 65 64 0a 20 20 69 6e 20 61 73 20 73  e used.  in as s
0d30: 65 71 75 65 6e 63 65 2e 20 46 6f 72 20 65 78 61  equence. For exa
0d40: 6d 70 6c 65 20 74 68 69 73 20 71 75 65 72 79 3a  mple this query:
0d50: 0a 0a 20 20 20 20 3c 63 6f 6c 3e 20 4d 41 54 43  ..    <col> MATC
0d60: 48 20 27 72 65 6c 69 61 62 6c 65 20 4e 45 41 52  H 'reliable NEAR
0d70: 2f 32 20 65 6e 67 69 6e 65 65 72 69 6e 67 20 4e  /2 engineering N
0d80: 45 41 52 2f 35 20 63 6f 6e 73 75 6c 74 61 6e 63  EAR/5 consultanc
0d90: 79 27 0a 0a 20 20 73 65 61 72 63 68 65 73 20 66  y'..  searches f
0da0: 6f 72 20 61 20 72 6f 77 20 74 68 61 74 20 63 6f  or a row that co
0db0: 6e 74 61 69 6e 73 20 61 6e 20 69 6e 73 74 61 6e  ntains an instan
0dc0: 63 65 20 6f 66 20 74 68 65 20 74 6f 6b 65 6e 20  ce of the token 
0dd0: 22 72 65 6c 69 61 62 6c 65 22 20 0a 20 20 73 65  "reliable" .  se
0de0: 70 65 72 61 74 65 64 20 62 79 20 6e 6f 74 20 6d  perated by not m
0df0: 6f 72 65 20 74 68 61 6e 20 74 77 6f 20 74 6f 6b  ore than two tok
0e00: 65 6e 73 20 66 72 6f 6d 20 61 6e 20 69 6e 73 74  ens from an inst
0e10: 61 6e 63 65 20 6f 66 20 22 65 6e 67 69 6e 65 65  ance of "enginee
0e20: 72 69 6e 67 22 2c 0a 20 20 77 68 69 63 68 20 69  ring",.  which i
0e30: 73 20 69 6e 20 74 75 72 6e 20 73 65 70 61 72 61  s in turn separa
0e40: 74 65 64 20 62 79 20 6e 6f 74 20 6d 6f 72 65 20  ted by not more 
0e50: 74 68 61 6e 20 35 20 6f 74 68 65 72 20 74 6f 6b  than 5 other tok
0e60: 65 6e 73 20 66 72 6f 6d 20 61 6e 0a 20 20 69 6e  ens from an.  in
0e70: 73 74 61 6e 63 65 20 6f 66 20 74 68 65 20 74 65  stance of the te
0e80: 72 6d 20 22 63 6f 6e 73 75 6c 74 61 6e 63 79 22  rm "consultancy"
0e90: 2e 20 50 68 72 61 73 65 73 20 65 6e 63 6c 6f 73  . Phrases enclos
0ea0: 65 64 20 69 6e 20 71 75 6f 74 65 73 20 6d 61 79  ed in quotes may
0eb0: 0a 20 20 61 6c 73 6f 20 62 65 20 75 73 65 64 20  .  also be used 
0ec0: 61 73 20 61 72 67 75 6d 65 6e 74 73 20 74 6f 20  as arguments to 
0ed0: 74 68 65 20 4e 45 41 52 20 6f 70 65 72 61 74 6f  the NEAR operato
0ee0: 72 2e 0a 0a 20 20 53 69 6d 69 6c 61 72 20 74 6f  r...  Similar to
0ef0: 20 74 68 65 20 4e 45 41 52 20 6f 70 65 72 61 74   the NEAR operat
0f00: 6f 72 2c 20 6f 6e 65 20 6f 72 20 6d 6f 72 65 20  or, one or more 
0f10: 74 6f 6b 65 6e 73 20 6f 72 20 70 68 72 61 73 65  tokens or phrase
0f20: 73 20 6d 61 79 20 62 65 20 0a 20 20 73 65 70 61  s may be .  sepa
0f30: 72 61 74 65 64 20 62 79 20 4f 52 20 6f 70 65 72  rated by OR oper
0f40: 61 74 6f 72 73 2e 20 49 6e 20 74 68 69 73 20 63  ators. In this c
0f50: 61 73 65 2c 20 6f 6e 6c 79 20 6f 6e 65 20 6f 66  ase, only one of
0f60: 20 74 68 65 20 73 70 65 63 69 66 69 65 64 20 74   the specified t
0f70: 6f 6b 65 6e 73 0a 20 20 6f 72 20 70 68 72 61 73  okens.  or phras
0f80: 65 73 20 6d 75 73 74 20 61 70 70 65 61 72 20 69  es must appear i
0f90: 6e 20 74 68 65 20 64 6f 63 75 6d 65 6e 74 2e 20  n the document. 
0fa0: 46 6f 72 20 65 78 61 6d 70 6c 65 2c 20 74 68 65  For example, the
0fb0: 20 71 75 65 72 79 3a 0a 0a 20 20 20 20 3c 63 6f   query:..    <co
0fc0: 6c 3e 20 4d 41 54 43 48 20 27 68 65 6c 6c 6f 20  l> MATCH 'hello 
0fd0: 4f 52 20 77 6f 72 6c 64 27 0a 0a 20 20 6d 61 74  OR world'..  mat
0fe0: 63 68 65 73 20 72 6f 77 73 20 74 68 61 74 20 63  ches rows that c
0ff0: 6f 6e 74 61 69 6e 20 65 69 74 68 65 72 20 74 68  ontain either th
1000: 65 20 74 65 72 6d 20 22 68 65 6c 6c 6f 22 2c 20  e term "hello", 
1010: 6f 72 20 74 68 65 20 74 65 72 6d 20 22 77 6f 72  or the term "wor
1020: 6c 64 22 2c 0a 20 20 6f 72 20 62 6f 74 68 2e 20  ld",.  or both. 
1030: 4e 6f 74 65 20 74 68 61 74 20 75 6e 6c 69 6b 65  Note that unlike
1040: 20 69 6e 20 6d 61 6e 79 20 70 72 6f 67 72 61 6d   in many program
1050: 6d 69 6e 67 20 6c 61 6e 67 75 61 67 65 73 2c 20  ming languages, 
1060: 74 68 65 20 4f 52 20 6f 70 65 72 61 74 6f 72 0a  the OR operator.
1070: 20 20 68 61 73 20 61 20 68 69 67 68 65 72 20 70    has a higher p
1080: 72 65 63 65 64 65 6e 63 65 20 74 68 61 6e 20 74  recedence than t
1090: 68 65 20 41 4e 44 20 6f 70 65 72 61 74 6f 72 73  he AND operators
10a0: 20 69 6d 70 6c 69 65 64 20 62 65 74 77 65 65 6e   implied between
10b0: 20 77 68 69 74 65 2d 73 70 61 63 65 0a 20 20 73   white-space.  s
10c0: 65 70 61 72 61 74 65 64 20 74 6f 6b 65 6e 73 2e  eparated tokens.
10d0: 20 54 68 65 20 66 6f 6c 6c 6f 77 69 6e 67 20 71   The following q
10e0: 75 65 72 79 20 6d 61 74 63 68 65 73 20 64 6f 63  uery matches doc
10f0: 75 6d 65 6e 74 73 20 74 68 61 74 20 63 6f 6e 74  uments that cont
1100: 61 69 6e 20 74 68 65 0a 20 20 74 65 72 6d 20 27  ain the.  term '
1110: 73 71 6c 69 74 65 27 20 61 6e 64 20 61 74 20 6c  sqlite' and at l
1120: 65 61 73 74 20 6f 6e 65 20 6f 66 20 74 68 65 20  east one of the 
1130: 74 65 72 6d 73 20 27 66 61 6e 74 61 73 74 69 63  terms 'fantastic
1140: 27 20 6f 72 20 27 69 6d 70 72 65 73 73 69 76 65  ' or 'impressive
1150: 27 2c 0a 20 20 6e 6f 74 20 74 68 6f 73 65 20 74  ',.  not those t
1160: 68 61 74 20 63 6f 6e 74 61 69 6e 20 62 6f 74 68  hat contain both
1170: 20 27 73 71 6c 69 74 65 27 20 61 6e 64 20 27 66   'sqlite' and 'f
1180: 61 6e 74 61 73 74 69 63 27 20 6f 72 20 27 69 6d  antastic' or 'im
1190: 70 72 65 73 73 69 76 65 27 3a 0a 0a 20 20 20 20  pressive':..    
11a0: 3c 63 6f 6c 3e 20 4d 41 54 43 48 20 27 73 71 6c  <col> MATCH 'sql
11b0: 69 74 65 20 66 61 6e 74 61 73 74 69 63 20 4f 52  ite fantastic OR
11c0: 20 69 6d 70 72 65 73 73 69 76 65 27 0a 0a 20 20   impressive'..  
11d0: 41 6e 79 20 74 6f 6b 65 6e 20 74 68 61 74 20 69  Any token that i
11e0: 73 20 70 61 72 74 20 6f 66 20 61 6e 20 46 74 73  s part of an Fts
11f0: 33 20 71 75 65 72 79 20 65 78 70 72 65 73 73 69  3 query expressi
1200: 6f 6e 2c 20 77 68 65 74 68 65 72 20 6f 72 20 6e  on, whether or n
1210: 6f 74 20 69 74 20 69 73 0a 20 20 70 61 72 74 20  ot it is.  part 
1220: 6f 66 20 61 20 70 68 72 61 73 65 20 65 6e 63 6c  of a phrase encl
1230: 6f 73 65 64 20 69 6e 20 71 75 6f 74 65 73 2c 20  osed in quotes, 
1240: 6d 61 79 20 68 61 76 65 20 61 20 27 2a 27 20 63  may have a '*' c
1250: 68 61 72 61 63 74 65 72 20 61 70 70 65 6e 64 65  haracter appende
1260: 64 20 74 6f 0a 20 20 69 74 2e 20 49 6e 20 74 68  d to.  it. In th
1270: 69 73 20 63 61 73 65 2c 20 74 68 65 20 74 6f 6b  is case, the tok
1280: 65 6e 20 6d 61 74 63 68 65 73 20 61 6c 6c 20 74  en matches all t
1290: 65 72 6d 73 20 74 68 61 74 20 62 65 67 69 6e 20  erms that begin 
12a0: 77 69 74 68 20 74 68 65 20 63 68 61 72 61 63 74  with the charact
12b0: 65 72 73 0a 20 20 6f 66 20 74 68 65 20 74 6f 6b  ers.  of the tok
12c0: 65 6e 2c 20 6e 6f 74 20 6a 75 73 74 20 74 68 6f  en, not just tho
12d0: 73 65 20 74 68 61 74 20 65 78 61 63 74 6c 79 20  se that exactly 
12e0: 6d 61 74 63 68 20 69 74 2e 20 46 6f 72 20 65 78  match it. For ex
12f0: 61 6d 70 6c 65 2c 20 74 68 65 20 0a 20 20 66 6f  ample, the .  fo
1300: 6c 6c 6f 77 69 6e 67 20 71 75 65 72 79 3a 0a 0a  llowing query:..
1310: 20 20 20 20 3c 63 6f 6c 3e 20 4d 41 54 43 48 20      <col> MATCH 
1320: 27 73 71 6c 2a 27 0a 0a 20 20 6d 61 74 63 68 65  'sql*'..  matche
1330: 73 20 61 6c 6c 20 72 6f 77 73 20 74 68 61 74 20  s all rows that 
1340: 63 6f 6e 74 61 69 6e 20 74 68 65 20 74 65 72 6d  contain the term
1350: 20 22 53 51 4c 69 74 65 22 2c 20 61 73 20 77 65   "SQLite", as we
1360: 6c 6c 20 61 73 20 74 68 6f 73 65 20 74 68 61 74  ll as those that
1370: 0a 20 20 63 6f 6e 74 61 69 6e 20 22 53 51 4c 22  .  contain "SQL"
1380: 2e 0a 0a 20 20 41 20 74 6f 6b 65 6e 20 74 68 61  ...  A token tha
1390: 74 20 69 73 20 6e 6f 74 20 70 61 72 74 20 6f 66  t is not part of
13a0: 20 61 20 71 75 6f 74 65 64 20 70 68 72 61 73 65   a quoted phrase
13b0: 20 6d 61 79 20 62 65 20 70 72 65 63 65 64 65 64   may be preceded
13c0: 20 62 79 20 61 20 27 2d 27 0a 20 20 63 68 61 72   by a '-'.  char
13d0: 61 63 74 65 72 2c 20 77 68 69 63 68 20 69 6e 64  acter, which ind
13e0: 69 63 61 74 65 73 20 74 68 61 74 20 6d 61 74 63  icates that matc
13f0: 68 69 6e 67 20 72 6f 77 73 20 6d 75 73 74 20 6e  hing rows must n
1400: 6f 74 20 63 6f 6e 74 61 69 6e 20 74 68 65 20 0a  ot contain the .
1410: 20 20 73 70 65 63 69 66 69 65 64 20 74 65 72 6d    specified term
1420: 2e 20 46 6f 72 20 65 78 61 6d 70 6c 65 2c 20 74  . For example, t
1430: 68 65 20 66 6f 6c 6c 6f 77 69 6e 67 3a 0a 0a 20  he following:.. 
1440: 20 20 20 3c 63 6f 6c 3e 20 4d 41 54 43 48 20 27     <col> MATCH '
1450: 22 64 61 74 61 62 61 73 65 20 65 6e 67 69 6e 65  "database engine
1460: 22 20 2d 73 71 6c 69 74 65 27 0a 0a 20 20 6d 61  " -sqlite'..  ma
1470: 74 63 68 65 73 20 72 6f 77 73 20 74 68 61 74 20  tches rows that 
1480: 63 6f 6e 74 61 69 6e 20 74 68 65 20 70 68 72 61  contain the phra
1490: 73 65 20 22 64 61 74 61 62 61 73 65 20 65 6e 67  se "database eng
14a0: 69 6e 65 22 20 62 75 74 20 64 6f 20 6e 6f 74 20  ine" but do not 
14b0: 63 6f 6e 74 61 69 6e 0a 20 20 74 68 65 20 74 65  contain.  the te
14c0: 72 6d 20 22 73 71 6c 69 74 65 22 2e 20 49 66 20  rm "sqlite". If 
14d0: 74 68 65 20 27 2d 27 20 63 68 61 72 61 63 74 65  the '-' characte
14e0: 72 20 6f 63 63 75 72 73 20 69 6e 73 69 64 65 20  r occurs inside 
14f0: 61 20 71 75 6f 74 65 64 20 70 68 72 61 73 65 2c  a quoted phrase,
1500: 0a 20 20 69 74 20 69 73 20 69 67 6e 6f 72 65 64  .  it is ignored
1510: 2e 20 49 74 20 69 73 20 70 6f 73 73 69 62 6c 65  . It is possible
1520: 20 74 6f 20 75 73 65 20 62 6f 74 68 20 74 68 65   to use both the
1530: 20 27 2d 27 20 70 72 65 66 69 78 20 61 6e 64 20   '-' prefix and 
1540: 74 68 65 20 27 2a 27 20 70 6f 73 74 66 69 78 0a  the '*' postfix.
1550: 20 20 6f 6e 20 61 20 73 69 6e 67 6c 65 20 74 65    on a single te
1560: 72 6d 2e 20 41 74 20 74 68 69 73 20 74 69 6d 65  rm. At this time
1570: 2c 20 61 6c 6c 20 46 74 73 33 20 71 75 65 72 69  , all Fts3 queri
1580: 65 73 20 6d 75 73 74 20 63 6f 6e 74 61 69 6e 20  es must contain 
1590: 61 74 20 6c 65 61 73 74 0a 20 20 6f 6e 65 20 74  at least.  one t
15a0: 65 72 6d 20 6f 72 20 70 68 72 61 73 65 20 74 68  erm or phrase th
15b0: 61 74 20 69 73 20 6e 6f 74 20 70 72 65 63 65 64  at is not preced
15c0: 65 64 20 62 79 20 74 68 65 20 27 2d 27 20 70 72  ed by the '-' pr
15d0: 65 66 69 78 2e 0a 0a 20 20 52 65 67 61 72 64 6c  efix...  Regardl
15e0: 65 73 73 20 6f 66 20 77 68 65 74 68 65 72 20 6f  ess of whether o
15f0: 72 20 6e 6f 74 20 61 20 74 61 62 6c 65 20 6e 61  r not a table na
1600: 6d 65 20 6f 72 20 63 6f 6c 75 6d 6e 20 6e 61 6d  me or column nam
1610: 65 20 69 73 20 75 73 65 64 20 6f 6e 20 74 68 65  e is used on the
1620: 20 0a 20 20 6c 65 66 74 20 68 61 6e 64 20 73 69   .  left hand si
1630: 64 65 20 6f 66 20 74 68 65 20 4d 41 54 43 48 20  de of the MATCH 
1640: 6f 70 65 72 61 74 6f 72 2c 20 61 20 73 70 65 63  operator, a spec
1650: 69 66 69 63 20 63 6f 6c 75 6d 6e 20 6f 66 20 74  ific column of t
1660: 68 65 20 66 74 73 33 20 74 61 62 6c 65 0a 20 20  he fts3 table.  
1670: 6d 61 79 20 62 65 20 61 73 73 6f 63 69 61 74 65  may be associate
1680: 64 20 77 69 74 68 20 65 61 63 68 20 74 6f 6b 65  d with each toke
1690: 6e 20 69 6e 20 61 20 71 75 65 72 79 20 62 79 20  n in a query by 
16a0: 70 72 65 63 65 64 69 6e 67 20 61 20 74 6f 6b 65  preceding a toke
16b0: 6e 20 77 69 74 68 0a 20 20 61 20 63 6f 6c 75 6d  n with.  a colum
16c0: 6e 20 6e 61 6d 65 20 66 6f 6c 6c 6f 77 65 64 20  n name followed 
16d0: 62 79 20 61 20 27 3a 27 20 63 68 61 72 61 63 74  by a ':' charact
16e0: 65 72 2e 20 46 6f 72 20 65 78 61 6d 70 6c 65 2c  er. For example,
16f0: 20 72 65 67 61 72 64 6c 65 73 73 20 6f 66 20 77   regardless of w
1700: 68 61 74 0a 20 20 69 73 20 73 70 65 63 69 66 69  hat.  is specifi
1710: 65 64 20 66 6f 72 20 3c 63 6f 6c 3e 2c 20 74 68  ed for <col>, th
1720: 65 20 66 6f 6c 6c 6f 77 69 6e 67 20 71 75 65 72  e following quer
1730: 79 20 72 65 71 75 69 72 65 73 20 74 68 61 74 20  y requires that 
1740: 63 6f 6c 75 6d 6e 20 22 63 6f 6c 31 22 0a 20 20  column "col1".  
1750: 6f 66 20 74 68 65 20 74 61 62 6c 65 20 63 6f 6e  of the table con
1760: 74 61 69 6e 73 20 74 68 65 20 74 65 72 6d 20 22  tains the term "
1770: 68 65 6c 6c 6f 22 2c 20 61 6e 64 20 74 68 61 74  hello", and that
1780: 20 63 6f 6c 75 6d 6e 20 22 63 6f 6c 32 22 20 6f   column "col2" o
1790: 66 20 74 68 65 0a 20 20 74 61 62 6c 65 20 63 6f  f the.  table co
17a0: 6e 74 61 69 6e 73 20 74 68 65 20 74 65 72 6d 20  ntains the term 
17b0: 22 77 6f 72 6c 64 22 2e 20 49 66 20 74 68 65 20  "world". If the 
17c0: 74 61 62 6c 65 20 64 6f 65 73 20 6e 6f 74 20 63  table does not c
17d0: 6f 6e 74 61 69 6e 20 63 6f 6c 75 6d 6e 73 0a 20  ontain columns. 
17e0: 20 6e 61 6d 65 64 20 22 63 6f 6c 31 22 20 61 6e   named "col1" an
17f0: 64 20 22 63 6f 6c 32 22 2c 20 74 68 65 6e 20 61  d "col2", then a
1800: 6e 20 65 72 72 6f 72 20 69 73 20 72 65 74 75 72  n error is retur
1810: 6e 65 64 20 61 6e 64 20 74 68 65 20 71 75 65 72  ned and the quer
1820: 79 20 69 73 0a 20 20 6e 6f 74 20 72 75 6e 2e 0a  y is.  not run..
1830: 0a 20 20 20 20 3c 63 6f 6c 3e 20 4d 41 54 43 48  .    <col> MATCH
1840: 20 27 63 6f 6c 31 3a 68 65 6c 6c 6f 20 63 6f 6c   'col1:hello col
1850: 32 3a 77 6f 72 6c 64 27 0a 0a 20 20 49 74 20 69  2:world'..  It i
1860: 73 20 6e 6f 74 20 70 6f 73 73 69 62 6c 65 20 74  s not possible t
1870: 6f 20 61 73 73 6f 63 69 61 74 65 20 61 20 73 70  o associate a sp
1880: 65 63 69 66 69 63 20 74 61 62 6c 65 20 63 6f 6c  ecific table col
1890: 75 6d 6e 20 77 69 74 68 20 61 20 71 75 6f 74 65  umn with a quote
18a0: 64 20 0a 20 20 70 68 72 61 73 65 20 6f 72 20 61  d .  phrase or a
18b0: 20 74 65 72 6d 20 70 72 65 63 65 64 65 64 20 62   term preceded b
18c0: 79 20 61 20 27 2d 27 20 6f 70 65 72 61 74 6f 72  y a '-' operator
18d0: 2e 20 41 20 27 2a 27 20 63 68 61 72 61 63 74 65  . A '*' characte
18e0: 72 20 6d 61 79 20 62 65 0a 20 20 61 70 70 65 6e  r may be.  appen
18f0: 64 65 64 20 74 6f 20 61 20 74 65 72 6d 20 61 73  ded to a term as
1900: 73 6f 63 69 61 74 65 64 20 77 69 74 68 20 61 20  sociated with a 
1910: 73 70 65 63 69 66 69 63 20 63 6f 6c 75 6d 6e 20  specific column 
1920: 66 6f 72 20 70 72 65 66 69 78 20 6d 61 74 63 68  for prefix match
1930: 69 6e 67 2e 0a 0a 33 2e 20 45 4e 48 41 4e 43 45  ing...3. ENHANCE
1940: 44 20 51 55 45 52 59 20 53 59 4e 54 41 58 0a 0a  D QUERY SYNTAX..
1950: 20 20 54 68 65 20 65 6e 68 61 6e 63 65 64 20 71    The enhanced q
1960: 75 65 72 79 20 73 79 6e 74 61 78 20 69 73 20 71  uery syntax is q
1970: 75 69 74 65 20 73 69 6d 69 6c 61 72 20 74 6f 20  uite similar to 
1980: 74 68 65 20 73 74 61 6e 64 61 72 64 20 71 75 65  the standard que
1990: 72 79 20 73 79 6e 74 61 78 2c 0a 20 20 77 69 74  ry syntax,.  wit
19a0: 68 20 74 68 65 20 66 6f 6c 6c 6f 77 69 6e 67 20  h the following 
19b0: 66 6f 75 72 20 64 69 66 66 65 72 65 6e 63 65 73  four differences
19c0: 3a 0a 0a 20 20 31 29 20 50 61 72 65 6e 74 68 65  :..  1) Parenthe
19d0: 73 69 73 20 61 72 65 20 73 75 70 70 6f 72 74 65  sis are supporte
19e0: 64 2e 20 57 68 65 6e 20 75 73 69 6e 67 20 74 68  d. When using th
19f0: 65 20 65 6e 68 61 6e 63 65 64 20 71 75 65 72 79  e enhanced query
1a00: 20 73 79 6e 74 61 78 2c 0a 20 20 20 20 20 70 61   syntax,.     pa
1a10: 72 65 6e 74 68 65 73 69 73 20 6d 61 79 20 62 65  renthesis may be
1a20: 20 75 73 65 64 20 74 6f 20 6f 76 65 72 63 6f 6d   used to overcom
1a30: 65 20 74 68 65 20 62 75 69 6c 74 2d 69 6e 20 70  e the built-in p
1a40: 72 65 63 65 64 65 6e 63 65 20 6f 66 20 74 68 65  recedence of the
1a50: 0a 20 20 20 20 20 73 75 70 70 6c 69 65 64 20 62  .     supplied b
1a60: 69 6e 61 72 79 20 6f 70 65 72 61 74 6f 72 73 2e  inary operators.
1a70: 20 46 6f 72 20 65 78 61 6d 70 6c 65 2c 20 74 68   For example, th
1a80: 65 20 66 6f 6c 6c 6f 77 69 6e 67 20 71 75 65 72  e following quer
1a90: 79 3a 0a 0a 20 20 20 20 20 20 20 3c 63 6f 6c 3e  y:..       <col>
1aa0: 20 4d 41 54 43 48 20 27 28 68 65 6c 6c 6f 20 77   MATCH '(hello w
1ab0: 6f 72 6c 64 29 20 4f 52 20 28 73 69 6d 70 6c 65  orld) OR (simple
1ac0: 20 65 78 61 6d 70 6c 65 29 27 0a 0a 20 20 20 20   example)'..    
1ad0: 20 6d 61 74 63 68 65 73 20 64 6f 63 75 6d 65 6e   matches documen
1ae0: 74 73 20 74 68 61 74 20 63 6f 6e 74 61 69 6e 20  ts that contain 
1af0: 62 6f 74 68 20 22 68 65 6c 6c 6f 22 20 61 6e 64  both "hello" and
1b00: 20 22 77 6f 72 6c 64 22 2c 20 61 6e 64 20 64 6f   "world", and do
1b10: 63 75 6d 65 6e 74 73 0a 20 20 20 20 20 74 68 61  cuments.     tha
1b20: 74 20 63 6f 6e 74 61 69 6e 20 62 6f 74 68 20 22  t contain both "
1b30: 73 69 6d 70 6c 65 22 20 61 6e 64 20 22 65 78 61  simple" and "exa
1b40: 6d 70 6c 65 22 2e 20 49 74 20 69 73 20 6e 6f 74  mple". It is not
1b50: 20 70 6f 73 73 69 62 6c 65 20 74 6f 20 66 6f 72   possible to for
1b60: 75 6d 6c 61 74 65 0a 20 20 20 20 20 73 75 63 68  umlate.     such
1b70: 20 61 20 71 75 65 72 79 20 75 73 69 6e 67 20 74   a query using t
1b80: 68 65 20 73 74 61 6e 64 61 72 64 20 73 79 6e 74  he standard synt
1b90: 61 78 2e 0a 0a 20 20 32 29 20 49 6e 73 74 65 61  ax...  2) Instea
1ba0: 64 20 6f 66 20 73 65 70 61 72 61 74 69 6e 67 20  d of separating 
1bb0: 74 6f 6b 65 6e 73 20 61 6e 64 20 70 68 72 61 73  tokens and phras
1bc0: 65 73 20 62 79 20 77 68 69 74 65 73 70 61 63 65  es by whitespace
1bd0: 2c 20 61 6e 20 41 4e 44 20 6f 70 65 72 61 74 6f  , an AND operato
1be0: 72 0a 20 20 20 20 20 6d 61 79 20 62 65 20 65 78  r.     may be ex
1bf0: 70 6c 69 63 69 74 6c 79 20 73 70 65 63 69 66 69  plicitly specifi
1c00: 65 64 2e 20 54 68 69 73 20 64 6f 65 73 20 6e 6f  ed. This does no
1c10: 74 20 63 68 61 6e 67 65 20 71 75 65 72 79 20 70  t change query p
1c20: 72 6f 63 65 73 73 69 6e 67 20 61 74 0a 20 20 20  rocessing at.   
1c30: 20 20 61 6c 6c 2c 20 62 75 74 20 6d 61 79 20 62    all, but may b
1c40: 65 20 75 73 65 64 20 74 6f 20 69 6d 70 72 6f 76  e used to improv
1c50: 65 20 72 65 61 64 61 62 69 6c 69 74 79 2e 20 46  e readability. F
1c60: 6f 72 20 65 78 61 6d 70 6c 65 2c 20 74 68 65 20  or example, the 
1c70: 66 6f 6c 6c 6f 77 69 6e 67 0a 20 20 20 20 20 71  following.     q
1c80: 75 65 72 79 20 69 73 20 68 61 6e 64 6c 65 64 20  uery is handled 
1c90: 69 64 65 6e 74 69 63 61 6c 6c 79 20 74 6f 20 74  identically to t
1ca0: 68 65 20 6f 6e 65 20 61 62 6f 76 65 3a 0a 0a 20  he one above:.. 
1cb0: 20 20 20 20 20 20 3c 63 6f 6c 3e 20 4d 41 54 43        <col> MATC
1cc0: 48 20 27 28 68 65 6c 6c 6f 20 41 4e 44 20 77 6f  H '(hello AND wo
1cd0: 72 6c 64 29 20 4f 52 20 28 73 69 6d 70 6c 65 20  rld) OR (simple 
1ce0: 41 4e 44 20 65 78 61 6d 70 6c 65 29 27 0a 0a 20  AND example)'.. 
1cf0: 20 20 20 20 41 73 20 77 69 74 68 20 74 68 65 20      As with the 
1d00: 4f 52 20 61 6e 64 20 4e 45 41 52 20 6f 70 65 72  OR and NEAR oper
1d10: 61 74 6f 72 73 2c 20 74 68 65 20 41 4e 44 20 6f  ators, the AND o
1d20: 70 65 72 61 74 6f 72 20 6d 75 73 74 20 62 65 20  perator must be 
1d30: 73 70 65 63 69 66 69 65 64 0a 20 20 20 20 20 69  specified.     i
1d40: 6e 20 75 70 70 65 72 20 63 61 73 65 2e 20 54 68  n upper case. Th
1d50: 65 20 77 6f 72 64 20 22 61 6e 64 22 20 73 70 65  e word "and" spe
1d60: 63 69 66 69 65 64 20 69 6e 20 6c 6f 77 65 72 20  cified in lower 
1d70: 6f 72 20 6d 69 78 65 64 20 63 61 73 65 20 69 73  or mixed case is
1d80: 20 0a 20 20 20 20 20 68 61 6e 64 6c 65 64 20 61   .     handled a
1d90: 73 20 61 20 72 65 67 75 6c 61 72 20 74 6f 6b 65  s a regular toke
1da0: 6e 2e 0a 0a 20 20 33 29 20 54 68 65 20 27 2d 27  n...  3) The '-'
1db0: 20 74 6f 6b 65 6e 20 70 72 65 66 69 78 20 69 73   token prefix is
1dc0: 20 6e 6f 74 20 73 75 70 70 6f 72 74 65 64 2e 20   not supported. 
1dd0: 49 6e 73 74 65 61 64 2c 20 61 20 6e 65 77 20 62  Instead, a new b
1de0: 69 6e 61 72 79 20 6f 70 65 72 61 74 6f 72 2c 0a  inary operator,.
1df0: 20 20 20 20 20 4e 4f 54 2c 20 69 73 20 69 6e 63       NOT, is inc
1e00: 6c 75 64 65 64 2e 20 54 68 65 20 4e 4f 54 20 6f  luded. The NOT o
1e10: 70 65 72 61 74 6f 72 20 72 65 71 75 69 72 65 73  perator requires
1e20: 20 74 68 61 74 20 74 68 65 20 71 75 65 72 79 20   that the query 
1e30: 73 70 65 63 69 66 69 65 64 0a 20 20 20 20 20 61  specified.     a
1e40: 73 20 69 74 73 20 6c 65 66 74 2d 68 61 6e 64 20  s its left-hand 
1e50: 6f 70 65 72 61 74 6f 72 20 6d 61 74 63 68 65 73  operator matches
1e60: 2c 20 62 75 74 20 74 68 61 74 20 74 68 65 20 71  , but that the q
1e70: 75 65 72 79 20 73 70 65 63 69 66 69 65 64 20 61  uery specified a
1e80: 73 20 74 68 65 0a 20 20 20 20 20 72 69 67 68 74  s the.     right
1e90: 2d 68 61 6e 64 20 6f 70 65 72 61 74 6f 72 20 64  -hand operator d
1ea0: 6f 65 73 20 6e 6f 74 2e 20 46 6f 72 20 65 78 61  oes not. For exa
1eb0: 6d 70 6c 65 2c 20 74 6f 20 71 75 65 72 79 20 66  mple, to query f
1ec0: 6f 72 20 61 6c 6c 20 72 6f 77 73 20 74 68 61 74  or all rows that
1ed0: 0a 20 20 20 20 20 63 6f 6e 74 61 69 6e 20 74 68  .     contain th
1ee0: 65 20 74 65 72 6d 20 22 65 78 61 6d 70 6c 65 22  e term "example"
1ef0: 20 62 75 74 20 6e 6f 74 20 74 68 65 20 74 65 72   but not the ter
1f00: 6d 20 22 73 69 6d 70 6c 65 22 2c 20 74 68 65 20  m "simple", the 
1f10: 66 6f 6c 6c 6f 77 69 6e 67 0a 20 20 20 20 20 71  following.     q
1f20: 75 65 72 79 20 63 6f 75 6c 64 20 62 65 20 75 73  uery could be us
1f30: 65 64 3a 0a 0a 20 20 20 20 20 20 20 3c 63 6f 6c  ed:..       <col
1f40: 3e 20 4d 41 54 43 48 20 27 65 78 61 6d 70 6c 65  > MATCH 'example
1f50: 20 4e 4f 54 20 73 69 6d 70 6c 65 27 0a 0a 20 20   NOT simple'..  
1f60: 20 20 20 41 73 20 66 6f 72 20 61 6c 6c 20 6f 74     As for all ot
1f70: 68 65 72 20 6f 70 65 72 61 74 6f 72 73 2c 20 74  her operators, t
1f80: 68 65 20 4e 4f 54 20 6f 70 65 72 61 74 6f 72 20  he NOT operator 
1f90: 6d 75 73 74 20 62 65 20 73 70 65 63 69 66 69 65  must be specifie
1fa0: 64 20 69 6e 0a 20 20 20 20 20 75 70 70 65 72 20  d in.     upper 
1fb0: 63 61 73 65 2e 20 4f 74 68 65 72 77 69 73 65 20  case. Otherwise 
1fc0: 69 74 20 77 69 6c 6c 20 62 65 20 74 72 65 61 74  it will be treat
1fd0: 65 64 20 61 73 20 61 20 72 65 67 75 6c 61 72 20  ed as a regular 
1fe0: 74 6f 6b 65 6e 2e 0a 0a 20 20 34 29 20 55 6e 6c  token...  4) Unl
1ff0: 69 6b 65 20 69 6e 20 74 68 65 20 73 74 61 6e 64  ike in the stand
2000: 61 72 64 20 73 79 6e 74 61 78 2c 20 77 68 65 72  ard syntax, wher
2010: 65 20 74 68 65 20 4f 52 20 6f 70 65 72 61 74 6f  e the OR operato
2020: 72 20 68 61 73 20 61 20 68 69 67 68 65 72 0a 20  r has a higher. 
2030: 20 20 20 20 70 72 65 63 65 64 65 6e 63 65 20 74      precedence t
2040: 68 61 6e 20 74 68 65 20 69 6d 70 6c 69 63 69 74  han the implicit
2050: 20 41 4e 44 20 6f 70 65 72 61 74 6f 72 2c 20 77   AND operator, w
2060: 68 65 6e 20 75 73 69 6e 67 20 74 68 65 20 65 6e  hen using the en
2070: 68 61 6e 63 65 64 0a 20 20 20 20 20 73 79 6e 74  hanced.     synt
2080: 61 78 20 69 6d 70 6c 69 63 69 74 20 61 6e 64 20  ax implicit and 
2090: 65 78 70 6c 69 63 74 20 41 4e 44 20 6f 70 65 72  explict AND oper
20a0: 61 74 6f 72 73 20 68 61 76 65 20 61 20 68 69 67  ators have a hig
20b0: 68 65 72 20 70 72 65 63 65 64 65 6e 63 65 0a 20  her precedence. 
20c0: 20 20 20 20 74 68 61 6e 20 4f 52 20 6f 70 65 72      than OR oper
20d0: 61 74 6f 72 73 2e 20 55 73 69 6e 67 20 74 68 65  ators. Using the
20e0: 20 65 6e 68 61 6e 63 65 64 20 73 79 6e 74 61 78   enhanced syntax
20f0: 2c 20 74 68 65 20 66 6f 6c 6c 6f 77 69 6e 67 20  , the following 
2100: 74 77 6f 0a 20 20 20 20 20 71 75 65 72 69 65 73  two.     queries
2110: 20 61 72 65 20 65 71 75 69 76 61 6c 65 6e 74 3a   are equivalent:
2120: 0a 0a 20 20 20 20 20 20 20 3c 63 6f 6c 3e 20 4d  ..       <col> M
2130: 41 54 43 48 20 27 73 71 6c 69 74 65 20 66 61 6e  ATCH 'sqlite fan
2140: 74 61 73 74 69 63 20 4f 52 20 69 6d 70 72 65 73  tastic OR impres
2150: 73 69 76 65 27 0a 20 20 20 20 20 20 20 3c 63 6f  sive'.       <co
2160: 6c 3e 20 4d 41 54 43 48 20 27 28 73 71 6c 69 74  l> MATCH '(sqlit
2170: 65 20 41 4e 44 20 66 61 6e 74 61 73 74 69 63 29  e AND fantastic)
2180: 20 4f 52 20 69 6d 70 72 65 73 73 69 76 65 27 0a   OR impressive'.
2190: 0a 20 20 20 20 20 68 6f 77 65 76 65 72 2c 20 77  .     however, w
21a0: 68 65 6e 20 75 73 69 6e 67 20 74 68 65 20 73 74  hen using the st
21b0: 61 6e 64 61 72 64 20 73 79 6e 74 61 78 2c 20 74  andard syntax, t
21c0: 68 65 20 71 75 65 72 79 3a 0a 0a 20 20 20 20 20  he query:..     
21d0: 20 20 3c 63 6f 6c 3e 20 4d 41 54 43 48 20 27 73    <col> MATCH 's
21e0: 71 6c 69 74 65 20 66 61 6e 74 61 73 74 69 63 20  qlite fantastic 
21f0: 4f 52 20 69 6d 70 72 65 73 73 69 76 65 27 0a 0a  OR impressive'..
2200: 20 20 20 20 20 69 73 20 65 71 75 69 76 61 6c 65       is equivale
2210: 6e 74 20 74 6f 20 74 68 65 20 65 6e 68 61 6e 63  nt to the enhanc
2220: 65 64 20 73 79 6e 74 61 78 20 71 75 65 72 79 3a  ed syntax query:
2230: 0a 0a 20 20 20 20 20 20 20 3c 63 6f 6c 3e 20 4d  ..       <col> M
2240: 41 54 43 48 20 27 73 71 6c 69 74 65 20 41 4e 44  ATCH 'sqlite AND
2250: 20 28 66 61 6e 74 61 73 74 69 63 20 4f 52 20 69   (fantastic OR i
2260: 6d 70 72 65 73 73 69 76 65 29 27 0a 0a 20 20 20  mpressive)'..   
2270: 20 20 54 68 65 20 70 72 65 63 65 64 65 6e 63 65    The precedence
2280: 20 6f 66 20 61 6c 6c 20 65 6e 68 61 6e 63 65 64   of all enhanced
2290: 20 73 79 6e 74 61 78 20 6f 70 65 72 61 74 6f 72   syntax operator
22a0: 73 2c 20 69 6e 20 6f 72 64 65 72 20 66 72 6f 6d  s, in order from
22b0: 20 68 69 67 68 65 73 74 0a 20 20 20 20 20 74 6f   highest.     to
22c0: 20 6c 6f 77 65 73 74 2c 20 69 73 3a 0a 0a 20 20   lowest, is:..  
22d0: 20 20 20 20 20 4e 45 41 52 20 20 20 20 20 20 20       NEAR       
22e0: 28 68 69 67 68 65 73 74 20 70 72 65 63 65 64 65  (highest precede
22f0: 6e 63 65 2c 20 74 69 67 68 74 65 73 74 20 67 72  nce, tightest gr
2300: 6f 75 70 69 6e 67 29 0a 20 20 20 20 20 20 20 4e  ouping).       N
2310: 4f 54 0a 20 20 20 20 20 20 20 41 4e 44 0a 20 20  OT.       AND.  
2320: 20 20 20 20 20 4f 52 20 20 20 20 20 20 20 20 20       OR         
2330: 28 6c 6f 77 65 73 74 20 70 72 65 63 65 64 65 6e  (lowest preceden
2340: 63 65 2c 20 6c 6f 6f 73 65 73 74 20 67 72 6f 75  ce, loosest grou
2350: 70 69 6e 67 29 0a 0a 20 20 55 73 69 6e 67 20 74  ping)..  Using t
2360: 68 65 20 61 64 76 61 6e 63 65 64 20 73 79 6e 74  he advanced synt
2370: 61 78 2c 20 69 74 20 69 73 20 70 6f 73 73 69 62  ax, it is possib
2380: 6c 65 20 74 6f 20 73 70 65 63 69 66 79 20 65 78  le to specify ex
2390: 70 72 65 73 73 69 6f 6e 73 20 65 6e 63 6c 6f 73  pressions enclos
23a0: 65 64 0a 20 20 69 6e 20 70 61 72 65 6e 74 68 65  ed.  in parenthe
23b0: 73 69 73 20 61 73 20 6f 70 65 72 61 6e 64 73 20  sis as operands 
23c0: 74 6f 20 74 68 65 20 4e 4f 54 2c 20 41 4e 44 20  to the NOT, AND 
23d0: 61 6e 64 20 4f 52 20 6f 70 65 72 61 74 6f 72 73  and OR operators
23e0: 2e 20 48 6f 77 65 76 65 72 20 62 6f 74 68 0a 20  . However both. 
23f0: 20 74 68 65 20 6c 65 66 74 20 61 6e 64 20 72 69   the left and ri
2400: 67 68 74 20 68 61 6e 64 20 73 69 64 65 20 6f 70  ght hand side op
2410: 65 72 61 6e 64 73 20 6f 66 20 4e 45 41 52 20 6f  erands of NEAR o
2420: 70 65 72 61 74 6f 72 73 20 6d 75 73 74 20 62 65  perators must be
2430: 20 65 69 74 68 65 72 0a 20 20 74 6f 6b 65 6e 73   either.  tokens
2440: 20 6f 72 20 70 68 72 61 73 65 73 2e 20 41 74 74   or phrases. Att
2450: 65 6d 70 74 69 6e 67 20 74 68 65 20 66 6f 6c 6c  empting the foll
2460: 6f 77 69 6e 67 20 71 75 65 72 79 20 77 69 6c 6c  owing query will
2470: 20 72 65 74 75 72 6e 20 61 6e 20 65 72 72 6f 72   return an error
2480: 3a 0a 0a 20 20 20 20 3c 63 6f 6c 3e 20 4d 41 54  :..    <col> MAT
2490: 43 48 20 27 73 71 6c 69 74 65 20 4e 45 41 52 20  CH 'sqlite NEAR 
24a0: 28 66 61 6e 74 61 73 74 69 63 20 4f 52 20 69 6d  (fantastic OR im
24b0: 70 72 65 73 73 69 76 65 29 27 0a 0a 20 20 51 75  pressive)'..  Qu
24c0: 65 72 69 65 73 20 6f 66 20 74 68 69 73 20 66 6f  eries of this fo
24d0: 72 6d 20 6d 75 73 74 20 62 65 20 72 65 2d 77 72  rm must be re-wr
24e0: 69 74 74 65 6e 20 61 73 3a 0a 0a 20 20 20 20 3c  itten as:..    <
24f0: 63 6f 6c 3e 20 4d 41 54 43 48 20 27 73 71 6c 69  col> MATCH 'sqli
2500: 74 65 20 4e 45 41 52 20 66 61 6e 74 61 73 74 69  te NEAR fantasti
2510: 63 20 4f 52 20 73 71 6c 69 74 65 20 4e 45 41 52  c OR sqlite NEAR
2520: 20 69 6d 70 72 65 73 73 69 76 65 27 0a            impressive'.