この記事の要点
SQL の ORDER BY で文字列カラムを数値順に並べたい場合のキャスト MySQL: ORDER BY CAST(col AS UNSIGNED) または col+0 PostgreSQL: ORDER BY col::integer または CAST(col AS INTEGER) 英数字混在 (例: "item_10" を数値ソート): 数値部分を抽出してキャスト 頻繁にキャストするくらいなら最初から INT 型にする方が高速

 

問題: 文字列カラムを数値順に並べる

-- VARCHAR カラムを単純に ORDER BY すると辞書順
SELECT id, name, age_str FROM users ORDER BY age_str;

-- 結果: 文字列としての比較
"10"  ← 先頭の "1" で並ぶ
"100"
"15"
"2"
"20"
"3"

-- 数値順にしたい (2, 3, 10, 15, 20, 100)
SELECT id, name, age_str FROM users ORDER BY CAST(age_str AS UNSIGNED);

MySQL

-- 方法 1: CAST
SELECT * FROM users
ORDER BY CAST(price_str AS UNSIGNED);

-- 方法 2: + 0 (暗黙の数値変換)
SELECT * FROM users
ORDER BY price_str + 0;

-- 方法 3: 符号付き
SELECT * FROM users
ORDER BY CAST(amount_str AS SIGNED);

-- 方法 4: DECIMAL (小数含む)
SELECT * FROM users
ORDER BY CAST(price_str AS DECIMAL(10,2));

-- LENGTH + 値 (桁数優先)
SELECT * FROM products
ORDER BY LENGTH(code), code;

PostgreSQL

-- 方法 1: ::int キャスト (簡潔)
SELECT * FROM users
ORDER BY age_str::integer;

-- 方法 2: CAST (標準 SQL)
SELECT * FROM users
ORDER BY CAST(age_str AS INTEGER);

-- 小数
SELECT * FROM users
ORDER BY price_str::numeric;

-- bigint
SELECT * FROM users
ORDER BY id_str::bigint;

Oracle

-- TO_NUMBER 関数
SELECT * FROM users
ORDER BY TO_NUMBER(age_str);

-- フォーマット指定
SELECT * FROM users
ORDER BY TO_NUMBER(amount_str, '9999.99');

SQL Server

-- CAST
SELECT * FROM users
ORDER BY CAST(age_str AS INT);

-- CONVERT
SELECT * FROM users
ORDER BY CONVERT(INT, age_str);

SQLite

-- CAST
SELECT * FROM users
ORDER BY CAST(age_str AS INTEGER);

-- 型なしの場合は自動で数値ソートする傾向もあるが、確実に CAST 推奨

英数字混在文字列のソート（自然順）

-- 課題: "item_2", "item_10", "item_1" を 1, 2, 10 の順にしたい

-- MySQL: SUBSTRING + CAST
SELECT * FROM items
ORDER BY
    CAST(SUBSTRING(code, 6) AS UNSIGNED);   -- "item_" の後ろの数値部分

-- 正規表現で抽出 (MySQL 8+)
SELECT * FROM items
ORDER BY
    CAST(REGEXP_SUBSTR(code, '[0-9]+') AS UNSIGNED);

-- PostgreSQL
SELECT * FROM items
ORDER BY (regexp_match(code, '[0-9]+'))[1]::int;

-- 一般化: 自然順ソート（natural sort）
-- MySQL では拡張機能必要、PostgreSQL は 9.6+ で
SELECT * FROM items
ORDER BY code COLLATE "natural";

NULL の扱い

-- NULL を最後に
SELECT * FROM users
ORDER BY CAST(age_str AS UNSIGNED) NULLS LAST;  -- PostgreSQL

-- NULL を最初に
ORDER BY CAST(age_str AS UNSIGNED) NULLS FIRST;

-- MySQL は NULLS 構文非対応 → ハック
ORDER BY age_str IS NULL, CAST(age_str AS UNSIGNED);
-- IS NULL は NULL=1, それ以外=0 → 0 が先に来る (NULL が最後)

キャスト失敗時の挙動

-- 「abc」のような数値変換できない値がある
SELECT * FROM users WHERE age_str = 'abc';

-- MySQL: 0 に変換 (警告のみ、エラーにならない)
ORDER BY CAST('abc' AS UNSIGNED);  -- → 0

-- 強い strict mode の場合エラー
SET sql_mode = "STRICT_ALL_TABLES";

-- PostgreSQL: エラー
SELECT 'abc'::integer;
-- ERROR: invalid input syntax for integer: "abc"

-- 安全なキャスト (NULL に変換)
SELECT NULLIF(REGEXP_REPLACE('abc', '[^0-9]', '', 'g'), '')::integer;
-- → NULL

パフォーマンス考慮

毎回 CAST するとインデックスが効かない:

-- age_str に INDEX があっても効かない
EXPLAIN SELECT * FROM users WHERE CAST(age_str AS UNSIGNED) > 30;
-- → 全件スキャン

-- 対処 1: 関数インデックス (MySQL 8+ / PostgreSQL)
CREATE INDEX idx_age_cast ON users ((CAST(age_str AS UNSIGNED)));

-- 対処 2: 計算カラム (MySQL 5.7+)
ALTER TABLE users ADD COLUMN age_int INT GENERATED ALWAYS AS (CAST(age_str AS UNSIGNED)) STORED;
CREATE INDEX idx_age_int ON users (age_int);

-- 対処 3: 最初から INT 型にする (推奨)
ALTER TABLE users MODIFY age INT;

日付の文字列ソート

-- "2026-05-15" 形式なら文字列のままでも ISO 形式は時系列順になる
SELECT * FROM events ORDER BY date_str;
-- OK: ISO 8601 形式の場合

-- "05/15/2026" 形式は ×
ORDER BY STR_TO_DATE(date_str, '%m/%d/%Y');  -- MySQL

-- PostgreSQL
ORDER BY TO_DATE(date_str, 'MM/DD/YYYY');

-- 推奨: DATE 型に変更
ALTER TABLE events MODIFY date_str DATE;

応用: IPv4 アドレスのソート

-- 文字列だと "10.0.0.1" が "9.0.0.1" より先になる
SELECT * FROM logs ORDER BY ip;
-- "10.0.0.1"
-- "192.168.1.1"
-- "9.0.0.1"

-- MySQL: INET_ATON で数値化
SELECT * FROM logs ORDER BY INET_ATON(ip);
-- "9.0.0.1"
-- "10.0.0.1"
-- "192.168.1.1"

-- PostgreSQL: INET 型を使う
SELECT * FROM logs ORDER BY ip::inet;

関連記事

• 現在値に加算する方法
• DB接続設定や値の取得(JdbcTemplate編)