no such function

What it means

SQLite looked up a function the statement calls and found none by that name, so preparing the statement fails with SQLITE_ERROR (code 1) and no such function: <name>. The name appears as you wrote it: no such function: REGEXP, no such function: now.

SQLite’s functions come from three places: those built into the library (which depend on its version and how it was compiled), those the program registers, and loaded extensions. So the same query can work in one program and fail in another, on the same file.

Calling a function that exists with the wrong number of arguments is a different error: wrong number of arguments to function count().

Common causes

1. A function from another database. NOW(), GETDATE(), DATE_FORMAT(), TO_CHAR(), LEFT(), LEN(), CHAR_LENGTH(), NVL() and GREATEST() aren’t SQLite functions.
2. REGEXP. SQLite parses x REGEXP y but leaves the regexp() function to the program. The sqlite3 shell adds one, so a query that works in the shell fails in your program. In our tests uuid() behaved the same way.
3. An older SQLite. concat(), concat_ws() and string_agg() arrived in 3.44.0, timediff() and octet_length() in 3.43.0, unixepoch() and format() in 3.38.0, if() in 3.48.0. JSON functions are built in from 3.38.0; before that they needed the JSON1 extension. The math functions (sqrt(), ln(), pi()…) arrived in 3.35.0 and exist only if SQLite was compiled with SQLITE_ENABLE_MATH_FUNCTIONS.
4. A view, trigger or index that uses a program’s own function. The program that created it registered the function; any other program that opens the file fails when it touches that view or trigger.
5. A typo: lenght.

How to fix it

Use SQLite’s own function

With two or more arguments, max() and min() compare their arguments; with one, they’re the usual aggregates.

Register the function in your program

For REGEXP, define regexp() on every connection that needs it. SQLite calls it with the pattern first: x REGEXP y becomes regexp(y, x). In Python:

import re, sqlite3

def regexp(pattern, value):
    return value is not None and re.search(pattern, value) is not None

connection = sqlite3.connect("app.db")
connection.create_function("regexp", 2, regexp, deterministic=True)

Other languages’ drivers have the same hook (sqlite3_create_function() in C). For a view or trigger that calls an application function, register it in each program that opens the file, or rewrite the view with built-in functions.

Check your SQLite version

SELECT sqlite_version();

Run it from the program that fails: the SQLite your program uses can differ from the sqlite3 shell’s. On the Mac we tested, the shell was 3.51.0 and Python’s sqlite3 module 3.53.4. If a function is missing because the version is old, rewrite the query (the table above) or upgrade the SQLite your program links. On recent versions, SELECT name FROM pragma_function_list WHERE name = 'concat'; shows whether a function is there.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0:

sqlite3 shop.db "SELECT now();"

Error: in prepare, no such function: now

The shell adds “Error: in prepare,”; SQLite’s message is no such function: now. date_format, to_char, left, nvl, getdate, len, greatest and char_length failed the same way, as did a typo:

Error: in prepare, no such function: lenght
  SELECT lenght('abc');
         ^--- error here

In the shell, REGEXP, uuid(), concat(), median() and if() worked. In Python 3.14’s sqlite3 module (SQLite 3.53.4), on the same file:

sqlite3.OperationalError: no such function: REGEXP

uuid() failed there too, while concat(), median() and sqrt() worked. After create_function("regexp", 2, regexp), the REGEXP query returned its row. A view created in the shell with WHERE name REGEXP '^A' failed in Python with the same message until the function was registered.

In Inlet

When SQLite rejects a function, Inlet shows the error at the position SQLite reports. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the failed statement, for example by rewriting NOW() with SQLite’s date functions.