Matching text in SQL

The MATCH operator takes a small query language of its own.

SELECT title
FROM doc_search
WHERE doc_search MATCH 'search';

SELECT title
FROM doc_search
WHERE doc_search MATCH 'index*';

SELECT title
FROM doc_search
WHERE doc_search MATCH '"full-text search"';

SELECT title
FROM doc_search
WHERE doc_search MATCH 'NEAR(read plan, 3)';

SELECT title
FROM doc_search
WHERE doc_search MATCH 'title : plans';

How it works

1. A bare word matches that word; word* matches it as a prefix.
2. Double quotes make a phrase; NEAR bounds the distance between terms.
3. column : term restricts a term to one indexed column.

Keywords and builtins used here

• FROM
• MATCH
• SELECT
• WHERE

The run, in numbers

Lines

19

Characters to type

344

Tokens

45

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 28 seconds.

Type this snippet

Step 2 of 4 in Full-text search, step 10 of 14 in JSON & search.

← Previous Next →