文字列と日付、エンジンごとの書き方

1

SELECT first_name || ' ' || last_name AS customer,
       country
FROM customers
LIMIT 10;

The same expression elsewhere

-- PostgreSQL, Oracle: identical to SQLite.
SELECT first_name || ' ' || last_name AS customer FROM customers;

-- MySQL / MariaDB: || means OR unless PIPES_AS_CONCAT is set.
SELECT CONCAT(first_name, ' ', last_name) AS customer FROM customers;

-- SQL Server: + concatenates, and CONCAT() exists from 2012.
SELECT first_name + ' ' + last_name AS customer FROM customers;
SELECT CONCAT(first_name, ' ', last_name) AS customer FROM customers;

3

SELECT order_number,
       ordered_at,
       strftime('%Y-%m', ordered_at) AS month,
       strftime('%d/%m/%Y', ordered_at) AS day_first
FROM orders
LIMIT 10;

Formatting a date elsewhere

-- MySQL / MariaDB
SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month FROM orders;

-- PostgreSQL, Oracle
SELECT to_char(ordered_at, 'YYYY-MM') AS month FROM orders;

-- SQL Server (2012+)
SELECT FORMAT(ordered_at, 'yyyy-MM') AS month FROM orders;
-- ...or, much faster in bulk, and the usual choice for grouping:
SELECT CONVERT(char(7), ordered_at, 126) AS month FROM orders;

5

SELECT o.order_number,
       o.ordered_at,
       s.delivered_at,
       ROUND(julianday(s.delivered_at) - julianday(o.ordered_at), 1) AS days_to_deliver
FROM orders o
JOIN shipments s ON s.order_id = o.id
WHERE s.delivered_at IS NOT NULL
ORDER BY days_to_deliver DESC
LIMIT 15;

Days between two dates elsewhere

-- MySQL / MariaDB: whole days, first argument minus second.
SELECT DATEDIFF(delivered_at, ordered_at) AS days FROM ...;

-- PostgreSQL: subtracting two timestamps gives an interval.
SELECT delivered_at - ordered_at AS elapsed FROM ...;
SELECT EXTRACT(DAY FROM delivered_at - ordered_at) AS days FROM ...;

-- SQL Server: the unit comes first, and the arguments are the other way round.
SELECT DATEDIFF(day, ordered_at, delivered_at) AS days FROM ...;

-- Oracle: subtracting two DATEs gives a number of days.
SELECT delivered_at - ordered_at AS days FROM ...;

7

SELECT id,
       first_name,
       phone,
       COALESCE(phone, '(none)') AS portable,
       IFNULL(phone, '(none)')   AS sqlite_and_mysql
FROM customers
WHERE phone IS NULL
LIMIT 10;

Replacing a NULL elsewhere

-- Everywhere, and the one to reach for:
SELECT COALESCE(phone, '(none)') FROM customers;

-- MySQL / MariaDB / SQLite
SELECT IFNULL(phone, '(none)') FROM customers;

-- SQL Server. Note ISNULL, not IFNULL, and it takes exactly two arguments.
SELECT ISNULL(phone, '(none)') FROM customers;

-- Oracle
SELECT NVL(phone, '(none)') FROM customers;