高度な関数

Q: 質問：CONCATがNULLに遭遇するとどうなりますか？

回答： MySQLでは、CONCATはNULLに遭遇するとNULLを返します。NULLを空文字列として扱いたい場合は、COALESCEを使用してください：CONCAT(COALESCE(first_name, ''), ' ', last_name)。PostgreSQLでも||演算子はNULLに遭遇するとNULLを返します。

Q: 質問：ROUNDとCEIL/FLOORの違いは何ですか？

回答： ROUNDは指定した小数位で四捨五入し、CEILは最も近い整数に切り上げ、FLOORは最も近い整数に切り捨てます。例えば、3.5：ROUND(3.5) = 4、CEIL(3.1) = 4、FLOOR(3.9) = 3。

Q: 質問：日付関数はデータベース間で大きく異なりますか？

回答： はい、差異は大きいです。MySQLはNOW()、DATEDIFF()、DATE_FORMAT()を使用し、SQL ServerはGETDATE()、DATEDIFF()、FORMAT()を使用し、PostgreSQLはNOW()、日付の減算、TO_CHAR()を使用します。クロスデータベース開発時は対応するドキュメントを参照してください。

Q: 質問：CASTとCONVERTのどちらを使うべきですか？

回答： CASTを使用してください — これはSQL標準であり、すべての主要データベースでサポートされています。CONVERTはMySQLとSQL Serverで構文が異なり、互換性が劣ります。

🌍 現実世界の例え

SQLはスイスアーミーナイフのようなものです — すでに学んだ「ナイフ」（クエリ）と「のこぎり」（集約）の他にも、 many便利なツールがあります：

文字列関数 = テキストエディタ — 結合、抽出、大文字小文字変換
数値関数 = 電卓 — 四捨五入、切り捨て、絶対値
日付関数 = カレンダー — 現在日付の取得、日数の差の計算、年月日の抽出
型変換 = 翻訳者 — 数値、テキスト、日付間の変換

これらの関数をマスターすることで、SQLでより多くのデータ処理ができ、アプリケーション層での追加処理の必要性を減らすことができます。

🎯 コアコンセプト

文字列関数

CONCAT — 文字列の結合

複数の文字列を1つに結合します。

SQL

-- 名前を結合
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- MySQLでは||による結合もサポートされています（PIPES_AS_CONCATの有効化が必要）
-- SELECT first_name || ' ' || last_name FROM employees;

SUBSTRING — 部分文字列の抽出

文字列の一部を抽出します。

SQL

-- メールアドレスのユーザー名部分を抽出（@の前）
SELECT email, SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM employees
WHERE email IS NOT NULL;

-- 簡単な抽出：位置1から3文字を取得
SELECT SUBSTRING('Hello World', 1, 3);  -- 結果：Hel

UPPER / LOWER — 大文字小文字の変換

SQL

SELECT UPPER('hello') AS uppercase, LOWER('WORLD') AS lowercase;
-- 結果：HELLO, world

-- 実用的な使い方：大文字小文字を区別しない検索
SELECT * FROM employees WHERE LOWER(email) = 'zhangsan@company.com';

TRIM — スペースの除去

SQL

-- 前後のスペースを除去
SELECT TRIM('  hello  ') AS trimmed;  -- 結果：hello

-- 指定した文字を除去
SELECT TRIM(LEADING '0' FROM '0012300');  -- 結果：12300

REPLACE — 文字列の置換

SQL

-- 電話番号の中間4桁を****でマスク
SELECT phone, REPLACE(phone, SUBSTRING(phone, 4, 4), '****') AS masked
FROM employees;

LENGTH — 文字列の長さ

SQL

SELECT first_name, LENGTH(first_name) AS name_len
FROM employees;

数値関数

ROUND — 四捨五入

SQL

SELECT ROUND(3.14159, 2);   -- 結果：3.14
SELECT ROUND(3.14159, 0);   -- 結果：3
SELECT ROUND(1234.5, -2);   -- 結果：1200（百の位で四捨五入）

CEIL / FLOOR — 切り上げ / 切り捨て

SQL

SELECT CEIL(3.1);    -- 結果：4（切り上げ）
SELECT CEIL(3.0);    -- 結果：3
SELECT FLOOR(3.9);   -- 結果：3（切り捨て）
SELECT FLOOR(3.0);   -- 結果：3

💡 注意：データベースによって関数名が異なります。SQL ServerはCEILING()を使用し、OracleはCEIL()を使用します。

ABS — 絶対値

SQL

SELECT ABS(-100);    -- 結果：100
SELECT ABS(50);      -- 結果：50

MOD — 剰余（余り）

SQL

SELECT MOD(10, 3);   -- 結果：1
SELECT MOD(100, 7);  -- 結果：2

-- SQL ServerはMOD関数をサポートしていないため、代わりに%を使用
-- SELECT 10 % 3;    -- 結果：1

日付関数

NOW — 現在の日付と時刻

SQL

SELECT NOW();        -- 結果：2026-06-28 14:30:00（現在時刻）
SELECT CURDATE();    -- 結果：2026-06-28（日付のみ）
SELECT CURTIME();    -- 結果：14:30:00（時刻のみ）

💡 注意：SQL ServerはNOW()の代わりにGETDATE()を使用します。

DATE — 日付部分の抽出

SQL

SELECT DATE(NOW());  -- 結果：2026-06-28

-- 年、月、日を抽出
SELECT EXTRACT(YEAR FROM order_date) AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       EXTRACT(DAY FROM order_date) AS day
FROM orders;

DATEDIFF — 日付の差

SQL

-- 2つの日付間の日数を計算
SELECT DATEDIFF('2026-12-31', '2026-01-01');  -- 結果：364

-- 従業員の勤続日数を計算
SELECT first_name, hire_date,
       DATEDIFF(CURDATE(), hire_date) AS tenure_days
FROM employees;

💡 注意：MySQLのDATEDIFF(date1, date2)はdate1 - date2を計算します。SQL Serverも同じ順序です。PostgreSQLはdate1 - date2で直接減算します。

DATE_FORMAT — 日付のフォーマット

SQL

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

-- SQL Server
-- SELECT FORMAT(order_date, 'yyyy-MM-dd') FROM orders;

EXTRACT — 日付部分の抽出

SQL

SELECT EXTRACT(YEAR FROM order_date) AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       EXTRACT(DAY FROM order_date) AS day,
       EXTRACT(HOUR FROM NOW()) AS hour
FROM orders;

型変換

CAST — 型変換（標準SQL）

SQL

-- 文字列を整数に変換
SELECT CAST('123' AS SIGNED INTEGER);  -- MySQL
SELECT CAST('123' AS INT);             -- SQL Server

-- 数値を文字列に変換
SELECT CAST(12345 AS CHAR);

-- 日付を文字列に変換
SELECT CAST(order_date AS CHAR) FROM orders;

-- 文字列を日付に変換
SELECT CAST('2026-06-28' AS DATE);

CONVERT — 型変換（MySQL / SQL Server）

SQL

-- MySQL
SELECT CONVERT('123', SIGNED INTEGER);
SELECT CONVERT(123, CHAR);

-- SQL Server
SELECT CONVERT(INT, '123');
SELECT CONVERT(VARCHAR, 12345);

💡 推奨：CASTはSQL標準であり、互換性が高いため推奨されます。CONVERTはデータベース間で構文の差異が大きいです。

📝 基本構文

SQL

-- 文字列関数
SELECT CONCAT(string1, string2) AS concatenated,
       SUBSTRING(string, start_position, length) AS extracted,
       UPPER(string) AS uppercase,
       LOWER(string) AS lowercase,
       TRIM(string) AS trimmed,
       REPLACE(string, old_value, new_value) AS replaced,
       LENGTH(string) AS length;

-- 数値関数
SELECT ROUND(number, decimal_places) AS rounded,
       CEIL(number) AS rounded_up,
       FLOOR(number) AS rounded_down,
       ABS(number) AS absolute,
       MOD(number1, number2) AS remainder;

-- 日付関数
SELECT NOW() AS current_time,
       CURDATE() AS current_date,
       DATEDIFF(date1, date2) AS day_difference,
       EXTRACT(YEAR FROM date) AS year;

-- 型変換
SELECT CAST(value AS target_type) AS converted;

💡 ヒント：

関数名やパラメータはデータベースによって異なる場合があります — 使用前に対応するデータベースのドキュメントを確認してください

CASTはSQL標準であり推奨されます；CONVERTはMySQLとSQL Serverで構文が異なります

日付関数は差異が最も大きいです — クロスデータベース開発時は特に注意してください

📌 例

例：文字列処理 — 従業員情報のフォーマット

SQL

SELECT 
    CONCAT(UPPER(last_name), ', ', first_name) AS name,
    LOWER(email) AS email,
    SUBSTRING(phone, 1, 3) AS area_code,
    LENGTH(first_name) AS first_name_length,
    REPLACE(department_id, NULL, 'Unassigned') AS department
FROM employees
ORDER BY last_name;

▶ 試してみよう

出力：

TEXT

name          email                   area_code  first_name_length  department
-----------  ----------------------  ---------  -----------------  ----------
LI, Si       lisi@company.com        138        2                  2
WANG, Wu     wangwu@company.com      139        2                  2
ZHANG, San   zhangsan@company.com    137        2                  1

💡 解説：CONCAT + UPPERで名前をフォーマットし、LOWERでメールを小文字に標準化し、SUBSTRINGで電話番号の最初の3桁を抽出します。

例：数値計算 — 注文金額の処理

SQL

SELECT 
    order_id,
    total_amount AS original_amount,
    ROUND(total_amount * 0.9, 2) AS discounted_amount,
    CEIL(total_amount / 1000) AS thousands_ceil,
    FLOOR(total_amount / 1000) AS thousands_floor,
    MOD(total_amount, 100) AS remainder
FROM orders
WHERE status = 'completed';

▶ 試してみよう

出力：

TEXT

order_id  original_amount  discounted_amount  thousands_ceil  thousands_floor  remainder
--------  --------------  -----------------  --------------  ---------------  ---------
1         15000.00        13500.00           15              15               0.00
2         8500.00         7650.00            9               8                0.00
3         3200.00         2880.00            4               3                0.00

💡 解説：ROUNDで10%割引後の価格を計算し、CEILとFLOORで千の位まで切り上げ・切り捨てし、MODで余りを計算します。

例：日付計算 + 型変換

SQL

SELECT 
    first_name AS name,
    hire_date AS hire_date,
    DATEDIFF(CURDATE(), hire_date) AS tenure_days,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    CONCAT(CAST(salary AS CHAR), ' yuan') AS salary_display,
    DATE_FORMAT(hire_date, '%Y-%m') AS hire_month
FROM employees
ORDER BY hire_date;

▶ 試してみよう

出力：

TEXT

name     hire_date   tenure_days  hire_year  salary_display  hire_month
------   ----------  -----------  ---------  --------------  ----------
Zhang    2023-03-15  1199         2023       15000.00 yuan   2023-03
Li       2023-07-01  1089         2023       18000.00 yuan   2023-07
Wang     2024-01-10  899          2024       12000.00 yuan   2024-01

💡 解説：DATEDIFFで勤続日数を計算し、EXTRACTで年を抽出し、CASTで給与を文字列に変換してから単位を結合し、DATE_FORMATで日付をフォーマットします。

🎬 実践シナリオ

シナリオ1：データクレンジング — フォーマットの標準化

データベースのデータはフォーマットが不統一で、表示のためにクレンジングが必要です。

SQL

SELECT 
    employee_id,
    CONCAT(UPPER(TRIM(last_name)), ', ', TRIM(first_name)) AS standard_name,
    LOWER(TRIM(email)) AS standard_email,
    REPLACE(REPLACE(phone, '-', ''), ' ', '') AS standard_phone,
    CAST(salary AS DECIMAL(10,2)) AS standard_salary
FROM employees
WHERE email IS NOT NULL;

💡 アプローチ：TRIMで余分なスペースを除去し、UPPER/LOWERで大文字小文字を標準化し、REPLACEで電話番号の特殊文字をクリーニングし、CASTでデータ型を統一します。

シナリオ2：レポート統計 — 時間ベースの分析

四半期ごとの注文データを分析します。

SQL

SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    CEIL(EXTRACT(MONTH FROM order_date) / 3.0) AS quarter,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 2) AS total_amount,
    ROUND(AVG(total_amount), 2) AS avg_order_amount,
    DATEDIFF(MAX(order_date), MIN(order_date)) AS span_days
FROM orders
WHERE status != 'cancelled'
GROUP BY EXTRACT(YEAR FROM order_date), CEIL(EXTRACT(MONTH FROM order_date) / 3.0)
ORDER BY year, quarter;

💡 アプローチ：CEIL(MONTH / 3.0)で月を四半期に変換します（1-3→Q1、4-6→Q2など）。DATEDIFFで各四半期の期間を計算します。

❓ よくある質問

質問：CONCATがNULLに遭遇するとどうなりますか？ 回答： MySQLでは、CONCATはNULLに遭遇するとNULLを返します。NULLを空文字列として扱いたい場合は、COALESCEを使用してください：CONCAT(COALESCE(first_name, ''), ' ', last_name)。PostgreSQLでも||演算子はNULLに遭遇するとNULLを返します。

質問：ROUNDとCEIL/FLOORの違いは何ですか？ 回答： ROUNDは指定した小数位で四捨五入し、CEILは最も近い整数に切り上げ、FLOORは最も近い整数に切り捨てます。例えば、3.5：ROUND(3.5) = 4、CEIL(3.1) = 4、FLOOR(3.9) = 3。

質問：日付関数はデータベース間で大きく異なりますか？ 回答： はい、差異は大きいです。MySQLはNOW()、DATEDIFF()、DATE_FORMAT()を使用し、SQL ServerはGETDATE()、DATEDIFF()、FORMAT()を使用し、PostgreSQLはNOW()、日付の減算、TO_CHAR()を使用します。クロスデータベース開発時は対応するドキュメントを参照してください。

質問：CASTとCONVERTのどちらを使うべきですか？ 回答： CASTを使用してください — これはSQL標準であり、すべての主要データベースでサポートされています。CONVERTはMySQLとSQL Serverで構文が異なり、互換性が劣ります。

📖 まとめ

カテゴリ	主な関数	用途
文字列	CONCAT, SUBSTRING, UPPER, LOWER, TRIM, REPLACE, LENGTH	結合、抽出、変換、クリーニング
数値	ROUND, CEIL, FLOOR, ABS, MOD	四捨五入、切り捨て、剰余
日付	NOW, CURDATE, DATEDIFF, EXTRACT, DATE_FORMAT	時刻取得、差の計算、部分抽出
型変換	CAST, CONVERT	データ型の変換

文字列関数はデータのクリーニングとフォーマットに使用します
数値関数は数学計算と精度制御に使用します
日付関数は時刻計算とレポート統計に使用します
CASTは標準SQLの型変換関数であり推奨されます
関数名や構文はデータベースによって異なる場合があります — クロスデータベース開発時に注意してください

📝 演習

演習1（⭐）：employeesテーブルをクエリし、first_nameとlast_nameをフルネーム（大文字）に結合し、各人の勤続日数を計算してください。

演習2（⭐⭐）：ordersテーブルをクエリし、月ごとの注文数と合計金額を集約し、EXTRACTで年と月を抽出し、ROUNDで小数点以下2桁を保持してください。

演習3（⭐⭐⭐）：employeesテーブルをクエリし、「社員名刺」を生成してください。フォーマット：[部門] 名前（入社年） - 月給：xxxxx yuan。文字列関数で結合し、CASTで数値を文字列に変換してください。

次のレッスン

👉 16-case-when - 条件式：CASE WHEN条件式を学び、SQLの条件ロジックをマスターしましょう！

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて