tinySQL - Function Examples

DATE AND TIME FUNCTIONS

STRING FUNCTIONS

NEW: REGEX FUNCTIONS

FULL-TEXT SEARCH FUNCTIONS

FTS_MATCH — boolean match against a query (term, phrase, boolean, wildcard)

FTS_WORD_COUNT — number of (stop-word-filtered, stemmed) tokens

ROW_TO_TEXT — ad-hoc whole-row substring search inside an ordinary WHERE

CONTAINS_ALL — every term must be present (case-insensitive substring match)

CONTAINS_ANY — at least one term must be present

CONTAINS_SCORE — count of matched terms (0..N) - use in ORDER BY to rank

NUMERIC FUNCTIONS

NEW: ARRAY FUNCTIONS

AGGREGATE FUNCTIONS

COUNT

COMPARISON AND RANGE PREDICATES

BETWEEN — inclusive range check, equivalent to (x >= lo AND x <= hi)

LIMIT / OFFSET

SQL — 2008 standard syntax: OFFSET n ROWS FETCH {FIRST|NEXT} m ROWS ONLY

CONDITIONAL AND NULL HANDLING

CRYPTOGRAPHIC AND ENCODING FUNCTIONS

JSON FUNCTIONS

TYPE CONVERSION AND INTROSPECTION

UTILITY FUNCTIONS

WINDOW FUNCTIONS - FULLY IMPLEMENTED

ROW_NUMBER — Assign unique row numbers

RANK — like ROW_NUMBER, but tied rows share a rank and the next rank

DENSE_RANK — like RANK, but no gaps after ties (e.g. 1, 1, 2)

NTILE — split the partition into N (approximately) equal-sized buckets

LAG — Access previous row value

LEAD — Access next row value

FIRST_VALUE — Get first value in window

LAST_VALUE — Get last value in window (up to current row by default)

MOVING_SUM — Calculate moving/rolling sum

MOVING_AVG — Calculate moving/rolling average

COMPLEX QUERIES COMBINING MULTIPLE FUNCTIONS

PIVOT

PRACTICAL USE CASES

HTTP — Fetch HTTP GET response (30 second timeout)

TABLE-VALUED FUNCTIONS (TVF)

TABLE_FROM_JSON — Parse JSON array into table

TABLE_FROM_JSON_LINES — Parse JSON Lines (JSONL) format

TABLE_FROM_CSV — Parse CSV data with configurable delimiter

SYSTEM CATALOG

JOB SCHEDULER

PRACTICAL EXAMPLES WITH NEW FUNCTIONS

GEOSPATIAL FUNCTIONS

GEO_POINT — build a GeoJSON Point from (lon, lat[, z])

GEO_DISTANCE — great-circle distance in meters between two points.

GEO_DWITHIN — true if two points are within a given radius (meters).

GEO_WITHIN_BBOX — true if a point falls inside [minLon, minLat, maxLon, maxLat].

GEO_BEARING — initial compass bearing (0-360 clockwise from north) from the

GEO_MIDPOINT — great-circle midpoint between two points. ST_MIDPOINT is the alias.

GEO_DESTINATION — project a point along a bearing for a distance (meters).

GEO_WITHIN_POLYGON — point-in-polygon(-or-multipolygon) test.

GEO_POLYGON_AREA — area in square meters, exterior ring minus holes.

GEO_LENGTH — sum of great-circle segment lengths of a LineString, in meters.

GEO_INTERSECTS — true if two geometries (point/line/polygon, any combination)

GEO_DISJOINT — the exact negation of GEO_INTERSECTS. ST_DISJOINT is the alias.

GEO_EQUALS — same coordinates, allowing for a different start vertex,

GEO_BUFFER — approximate a circular buffer (meters) around a point as a

GEO_CONVEX_HULL — convex hull of every vertex in a geometry, as a Polygon.

GEO_ENVELOPE — a geometry's bounding box as a Polygon (vs. GEO_BBOX's plain

GEO_LINE_INTERPOLATE — the point a fraction (0-1) of the way along a

GEO_CLIP — Sutherland-Hodgman clip of a geometry to a convex boundary

GEOSPATIAL — EDITING AND QUALITY

GEO_SIMPLIFY — reduce vertex count. Accepts Douglas-Peucker ('dp', the

GEO_BBOX — bounding box as [minLon, minLat, maxLon, maxLat]. ST_BBOX is the alias.

GEO_CENTROID — area/length-weighted centroid of a geometry. ST_CENTROID is the alias.

GEO_AFFINE — shift, scale, and rotate a geometry around an anchor (default

GEO_SMOOTH — Chaikin corner-cutting smoothing, 0-8 iterations. ST_SMOOTH is the alias.

GEO_DROP_HOLES — remove every hole from a Polygon/MultiPolygon, keeping only

GEO_CLEAN — remove repeated consecutive vertices and normalize ring closure.

GEO_SNAP — round coordinates to a grid, then clean. ST_SNAPTOGRID is the alias.

GEO_IS_VALID — structural GeoJSON check (not full topology validation --

GEOSPATIAL — REGION OPERATIONS, SEARCH, AND CLASSIFICATION

GEO_DISSOLVE — merge every geometry in a group into one, by cancelling

GEO_BBOX_AGG — bounding box across every geometry in a group.

GEO_CENTROID_AGG — (optionally weighted) centroid across a group. The

GEO_SEARCH — an indexed bbox/radius search over a table, backed by a lazy,

TILE FUNCTIONS

TILE_ZXY — the covering tile at one call, including the MBTiles TMS row

TILE_FLIP_Y — convert an XYZ row to/from the MBTiles TMS tile_row (its own

TILE_BBOX — a tile's geographic bounds as [west, south, east, north]

TILE_PARENT — the containing tile one zoom level up (NULL at zoom 0)

TILE_COUNT — how many tiles a fully populated zoom level holds (4^zoom)

TILE_CONTAINS — whether a tile covers a point (edges belong to the tile on