# info > DBD::SQLite::Fulltext_search

---
type: CommandReference
command: DBD::SQLite::Fulltext_search
mode: perldoc
section: 3pm
source: perldoc
---

## Quick Reference
- `CREATE VIRTUAL TABLE t USING fts4(content)` — create FTS4 table with single column
- `INSERT INTO t(content) VALUES (?)` — insert documents
- `SELECT * FROM t WHERE t MATCH 'linux'` — search for single term
- `SELECT * FROM t WHERE t MATCH 'lin*'` — search for prefix
- `SELECT * FROM t WHERE t MATCH '"linux applications"'` — phrase search
- `SELECT * FROM t WHERE t MATCH 'sqlite NEAR/6 database'` — proximity search
- `SELECT * FROM t WHERE t MATCH 'linux AND problems'` — boolean AND (implicit by default)
- `SELECT docid, snippet(t) FROM t WHERE t MATCH 'foo'` — use snippet() for excerpts

## Name
DBD::SQLite::Fulltext_search — Using fulltext searches with DBD::SQLite

## Synopsis
sql
CREATE VIRTUAL TABLE table_name USING fts4(column1, column2, ...);
SELECT * FROM table_name WHERE table_name MATCH 'query';
## Options
- **Query Syntax**:
  - `MATCH 'term'` — match documents containing a term.
  - `MATCH 'term*'` — match prefix.
  - `MATCH '^term'` — term must be first in field.
  - `MATCH 'column:term'` — restrict search to a specific column.
  - `MATCH '"phrase query"'` — phrase search (exact order, no intervening tokens).
  - `MATCH 'term1 NEAR/N term2'` — proximity search, default N=10.
  - `MATCH 'term1 AND term2'`, `OR`, `NOT` — boolean set operations (AND is implicit).
  - `snippet(table)` — build formatted excerpt with highlighted terms.
  - `offsets(table)` — return byte offsets of matched terms.
  - `matchinfo(table)` — return match information (used in contentless tables).

- **Tokenizers**:
  - `tokenize=simple` — case-insensitive ASCII, alphanumeric + underscore + UTF≥128.
  - `tokenize=porter` — same as `simple` plus Porter stemming.
  - `tokenize=icu` — ICU-based Unicode tokenization (requires SQLITE_ENABLE_ICU).
  - `tokenize=unicode61` — full Unicode case folding, separates on Unicode space/punctuation; optional `remove_diacritics=0|1`.
  - `tokenize=perl 'Package::function'` — custom Perl tokenizer; function returns a closure that returns `($term, $len, $start, $end, $index)`.

- **Storage Options**:
  - `content=""` — contentless table: stores only the index, not the original document. Requires explicit `docid` on INSERT. Cannot UPDATE/DELETE. `snippet()` and `offsets()` not available; `matchinfo()` may be used.
  - Default: full copy of documents stored with index.

## Examples
sql
-- Create and populate FTS4 table
CREATE VIRTUAL TABLE fts_example USING fts4(content);
INSERT INTO fts_example(content) VALUES ('SQLite is an ACID compliant embedded relational database management system');

-- Basic search with snippet
SELECT docid, snippet(fts_example) FROM fts_example WHERE content MATCH 'sqlite';

-- Prefix search
SELECT * FROM fts_example WHERE fts_example MATCH 'lin*';

-- Phrase search
SELECT * FROM fts_example WHERE fts_example MATCH '"ACID compliant"';

-- NEAR search with custom proximity
SELECT * FROM fts_example WHERE fts_example MATCH 'sqlite NEAR/2 database';

-- Boolean combination
SELECT * FROM fts_example WHERE fts_example MATCH 'sqlite AND database';

-- Contentless table (must specify docid)
CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b);
INSERT INTO t1(docid, a, b) VALUES(1, 'a b c', 'd e f');

-- Use bind_param with explicit type for docid
use DBI qw/SQL_INTEGER/;
my $sql = "INSERT INTO t1(docid, a, b) VALUES(?, ?, ?)";
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, 2, SQL_INTEGER);
$sth->bind_param(2, "aa");
$sth->bind_param(3, "bb");
$sth->execute();
## See Also
- [SQLite FTS3/FTS4 documentation](http://www.sqlite.org/fts3.html)
- [DBD::SQLite](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3ASQLite/markdown)
- [DBD::SQLite::FTS3Transitional](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3ASQLite%3A%3AFTS3Transitional/markdown) — for legacy `+`/`-` query syntax
- [Search::Tokenizer](https://www.chedong.com/phpMan.php/perldoc/Search%3A%3ATokenizer/markdown) — pre-built Perl tokenizers

## Exit Codes
Not documented.