Excel
Date Functions in Excel Versus SQL Date Functions Compared
Translate Excel date functions to SQL: YEAR, EOMONTH, EDATE, DATEDIF, NETWORKDAYS, WEEKDAY, WEEKNUM and TEXT, with worked results and week numbering traps.
7 min read
Library
Everything you need to level up your coding skills.
Editor's pick
CSVGuide
Importing CSV Files Into SQLite From the Command Line
The sqlite3 .import command loads a CSV in seconds and reports problems as warnings with exit status zero. Import safely with types, checks and a swap.
20 min read
XML
Choosing Between XSD RELAX NG and Schematron for XML Checks
The same order checked three ways: XSD and RELAX NG passed a wrong total and a negative price, Schematron caught both, only RELAX NG took reordered children.
7 min read
CSV
Fixing Data Truncated Warnings in a MySQL CSV Load
LOAD DATA LOCAL clips long strings and out of range numbers to the nearest value and only warns. See each warning, the checks that find them, and the fix.
9 min read
YAMLGuide
Working With YAML From the Terminal Using yq and Friends
A tested command-line guide to YAML: query, edit, merge, convert, lint, validate and diff with yq, yamllint and check-jsonschema, plus the traps found.
20 min read
Excel
Text Functions in Excel Versus SQL String Functions
Map Excel text functions to SQL string functions: LEFT, MID, LEN, TRIM, SUBSTITUTE, FIND, TEXTJOIN, PROPER and TEXT, with the edge cases for spaces and nulls.
7 min read
JSON
Expanding JSONB Into Typed Rows With jsonb_to_recordset
Turn a JSONB array of objects into typed SQL columns with jsonb_to_recordset, jsonb_populate_recordset and jsonb_each, with the errors and edge cases.
7 min read
CSV
Importing a Large CSV Into MySQL Without Locking the Table
Measure what other sessions see while LOAD DATA runs on InnoDB, whether chunking helps, and how a build and swap with RENAME TABLE avoids any visible window.
8 min read
XML
Common XSD Validation Errors Explained With the Fix for Each
Ten XSD validation messages from libxml2, exactly as printed, what each one means in the document and the smallest change that makes it validate.
7 min read
Excel
Removing Duplicates in Excel Versus SELECT DISTINCT
Reproduce Excel's Remove Duplicates in SQL: how DISTINCT and ROW_NUMBER choose the row to keep, how case and spaces matter, and how to delete duplicates.
7 min read
PostgreSQL
Typo Tolerant Search With pg_trgm in PostgreSQL Queries
Build typo tolerant search with pg_trgm in PostgreSQL: trigrams, similarity thresholds, GIN and GiST indexes, word similarity and full text fallback.
7 min read
CSV
Loading a CSV Into MySQL Workbench Versus the Command Line
Use a GUI import wizard for small files and LOAD DATA for big ones. See the measured gap between row at a time and bulk loading, and what a GUI sends.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.