SuperAPI AI Directory

functions

Coding & development

View repository

Updated 11-01 16:10

About this skill

When to use

Pick this skill when the question is about SQL syntax — what functions can I call inside a SELECT / WHERE clause. For the table schemas themselves, see the connect skill's references/schema-catalog.md.

Helper functions added by r2xsql

function returns description
regexp(pattern, text) INT 1/0 ECMAScript-regex match; backs text REGEXP 'pat'

regexp() backs text REGEXP 'pat' for WHERE/SELECT filtering. It is the only non-stock function, and the only one that ever will be: SQLite reserves the REGEXP syntax but ships no implementation, so the operator is a runtime error unless a host registers it. There is no radare2 command that expresses a regex match, which is what makes it different from everything that used to be on this list. Nothing here mutates the session; all other functions below are stock SQLite.

Project save/open and type definition used to be SQL functions here. They are radare2 commands — Ps <name>, P <name>, td <decl> — reaching you over the same transport as the SQL, so a SQL wrapper bought nothing. SELECT name FROM projects and SELECT name FROM types still read the results back.

Formatting addresses

Addresses in the schema are stored as INT64; humans want hex. Use SQLite's printf:

expression result note
printf('0x%x', addr) 0x401000 pretty 0x… address form
hex(addr) 34303130… SQLite built-in: hex of the bytes, not 0x…
unhex('4889e5') BLOB SQLite built-in (3.41+): hex string → raw bytes

Note hex() is the standard SQLite function (uppercase hex of the argument's bytes) — it is not an address formatter. Reach for printf('0x%x', addr) when you want 0x….

SQLite built-ins

The full SQLite function catalog is available.

function use case
LIKE name LIKE 'aes_%' — glob-style match
GLOB name GLOB '*[Aa]es*' — Unix-glob, case-sens.
INSTR(s, sub) position of sub in s (1-based, 0 = miss)
SUBSTR(s, i, n) slice s
LENGTH(s) character length
LOWER(s) / UPPER(s) case folding
COALESCE(a, b) first non-NULL
IIF(c, a, b) inline conditional
CAST(x AS TEXT) force type coercion
CASE WHEN … END full SQL case
JSON_EXTRACT(j,'$.k') extract a field from a JSON string value

Common idioms

-- format addresses for human-readable output
SELECT printf('0x%x', addr) AS at, name FROM funcs LIMIT 5;
-- ┌──────────┬─────────┐
-- │ at       │ name    │
-- │ 0x401000 │ sub_…   │

-- regex against function names (r2xsql-registered regexp())
SELECT addr, name FROM funcs WHERE name REGEXP '^aes_(enc|dec)_';

-- pretty address alongside the raw instruction bytes
SELECT printf('0x%x', addr) AS at, bytes FROM instructions LIMIT 5;

-- conditional column derived from another
SELECT name,
       CASE WHEN cc > 25 THEN 'complex'
            WHEN cc > 10 THEN 'medium'
            ELSE 'simple' END AS rating
FROM funcs;

-- count then sort
SELECT module, COUNT(*) AS n
FROM imports
GROUP BY module
HAVING n > 1
ORDER BY n DESC;