MENU

SQL Server varchar(max) を固定長分割してPartId付き複数レコードにする方法（PartId=1しか出ない原因と解決）

Microsoft

SQL Server の varchar(max) に入った長文コメントを、64,750 文字ごとに分割して PartId 付きの複数行にしたいのに PartId=1 しか出ない…。原因の切り分けと、再帰CTE/数表で確実に分割する実装をまとめます。

目次

やりたいこと：varchar(max) の長文を固定長で分割し、PartId を振って複数レコード化する

たとえば次のような要件です。

  • テーブルの Comments 列（varchar(max)） に長文が入っている（例：数十万文字）
  • 外部システムへの連携や移行の都合で、一定文字数（例：64,750 文字）ごとに区切って複数行にしたい
  • 分割した各行には PartId（1,2,3…） を付け、あとで順序どおりに結合できるようにしたい

ところが「分割クエリは書けているはずなのに、実行結果が PartId=1 の 1 行だけになる」という現象が起こります。ここで最初に疑うべきは、分割ロジックではなく入力データが想定どおりの長さになっていないケースです。

PartId=1 しか返らないときの最短チェック：本当に 64,750 文字を超えているか

分割が 1 行しか返らない場合、クエリが間違っている前に「Comments の実長が 64,750 文字以下」になっている可能性が高いです。まずは対象行の長さを確認します。

SELECT
  LEN(Comments)        AS LenChars,   -- 文字数（末尾スペースは数えない）
  DATALENGTH(Comments) AS Bytes       -- バイト数（末尾スペースも含む）
FROM dbo.YourTable
WHERE Id = 1;

ここで LenChars が 8,000 付近で止まっていたり、そもそも 64,750 未満なら、分割クエリは正しくても PartId は増えません。特に、テストデータ生成で REPLICATE を使っている場合は要注意です。

原因の定番：REPLICATE が 8,000 文字（nvarchar なら 4,000 文字）で頭打ちになっている

REPLICATE は「入力式のデータ型」を引き継ぐ性質があります。つまり、入力が varchar(1) のリテラル 'A' のままだと、戻り値も varchar 扱いになり、結果として 最大 8,000 文字で切り捨てが発生します（nvarchar の場合は 4,000 文字）。

よくある “罠” は次のようなテストデータです。

-- NG：'A' は varchar(1) 扱いになりやすく、結果が 8,000 文字で頭打ちになりがち
INSERT INTO dbo.YourTable(Id, Comments)
VALUES (1, REPLICATE('A', 280000));

そして、結果だけを見て「分割できない」と判断してしまう…という流れです。ポイントは、列が varchar(max) でも、生成された文字列が短ければそのまま短いまま入るということ。後から CAST しても、切り捨てられた文字は戻りません。

-- NG：すでに短くなった後なので意味がない
SELECT CAST(REPLICATE('A', 280000) AS varchar(max));

対処：REPLICATE の入力を varchar(max) に明示してから長文を生成する

正しく長文を作るには、REPLICATE の「入力側」を varchar(max) に寄せます。

-- OK：入力を varchar(max) にする（重要）
INSERT INTO dbo.YourTable(Id, Comments)
VALUES (1, REPLICATE(CAST('A' AS varchar(max)), 280000));

実データが Unicode（日本語を含む）で nvarchar(max) を使っているなら、同様に nvarchar(max) で揃えます。

-- nvarchar(max) の場合
INSERT INTO dbo.YourTable(Id, Comments)
VALUES (1, REPLICATE(CAST(N'あ' AS nvarchar(max)), 280000));

生成後にもう一度、LEN と DATALENGTH を確認してください。ここが通れば、分割クエリが PartId=1 だけになる問題はかなりの確率で解決します。

よくある原因と確認方法の早見表

症状原因候補確認SQL対処
PartId=1 しか出ない実データが 64,750 文字未満（テストデータが短い）SELECT LEN(Comments) FROM ...まず長さを確認し、生成方法を見直す
LEN が 8,000 付近で止まるREPLICATE の入力が varchar で戻り値が 8,000 で頭打ちSELECT LEN(REPLICATE('A',280000))REPLICATE(CAST('A' AS varchar(max)),...)
再帰CTEで途中までしか出ないMAXRECURSION の既定（100）に引っかかっている警告メッセージを確認OPTION (MAXRECURSION 0) を付ける
末尾の空白が消えるLEN は末尾スペースを数えないLEN と DATALENGTH を比較必要なら DATALENGTH を使って判定する

分割の考え方：開始位置（StartPos）を固定長で増やし、SUBSTRING で切り出す

固定長分割の基本はシンプルです。

  • PartId=1 の開始位置は 1
  • PartId=2 の開始位置は 1 + チャンクサイズ
  • PartId=3 の開始位置は 1 + チャンクサイズ×2
  • …というように開始位置を増やしていく

切り出し自体は SUBSTRING(Comments, 開始位置, チャンクサイズ) でOKです。SQL Server の SUBSTRING は、指定した長さに満たない終端でもエラーにはならず、残りだけ返してくれます。つまり最後のパーツだけ短くなるのは自然な挙動です。

解決策：再帰CTEで PartId を生成しながら固定長分割する

対象が少数行で、実装を分かりやすく書きたいなら再帰CTEが手っ取り早いです。チャンクサイズを変数化しておけば、要件変更にも対応しやすくなります。

DECLARE @Chunk int = 64750;

;WITH Split AS (
  -- 1行目（PartId=1）
  SELECT
    t.Id,
    CAST(1 AS int) AS PartId,
    CAST(1 AS int) AS StartPos,
    t.Comments
  FROM dbo.YourTable AS t
  WHERE t.Id = 1
    AND t.Comments IS NOT NULL

  UNION ALL

  -- 2行目以降（StartPos を @Chunk ずつ進める）
  SELECT
    s.Id,
    s.PartId + 1,
    s.StartPos + @Chunk,
    s.Comments
  FROM Split AS s
  WHERE s.StartPos + @Chunk <= LEN(s.Comments)
)
SELECT
  Id,
  PartId,
  SUBSTRING(Comments, StartPos, @Chunk) AS CommentPart
FROM Split
ORDER BY PartId
OPTION (MAXRECURSION 0);

ポイントは次のとおりです。

  • 終端条件：次の開始位置が LEN(Comments) を超えない間だけ再帰する
  • MAXRECURSION：分割数が 100 を超える可能性があるなら必ず指定する（デフォルトは 100）
  • NULL対策：NULL のときは展開しない（必要なら COALESCE で空文字に寄せる）

例えば 280,000 文字を 64,750 文字で切ると、概ね次のようになります。

PartId開始位置想定される長さ説明
1164,750先頭から固定長
264,75164,7502ブロック目
3129,50164,7503ブロック目
4194,25164,7504ブロック目
5259,001残り（短くなる）最後の端数

再帰CTEがハマりやすいポイントと改善のコツ

再帰CTEは読みやすい反面、件数が増えると詰まりやすい箇所があります。実務でよく当たるポイントをまとめます。

  • 分割数が多いと遅い：1行あたり数百パーツになるような設計（チャンクが小さい等）だと、再帰のコストが目立ちます。
  • 複数行をまとめて分割しづらい：再帰CTEは「1行の Comments を順番に進める」書き方が素直で、全行一括処理は工夫が必要です。
  • LEN の特性：末尾スペースを含めて厳密に分割したい場合は DATALENGTH ベースの判定に寄せる必要があります。

「対象行が多い」「夜間バッチで何万行も分割する」「分割後テーブルに保存する」などの用途なら、次の数表（Tally）方式が安定します。

おすすめ：数表（Tally）を使った set-based 分割（複数行でも安定）

数表（Tally）とは 1,2,3… の連番を生成し、その連番を使って開始位置を計算する方法です。ループや再帰に頼らず、SQL Server が得意な set-based 処理に寄せられます。

下の例は sys.all_objects をクロス結合して十分な行数を確保し、各レコードごとに必要な PartId 分だけ連番を作ります（環境差はありますが、クロス結合で数百万行以上は作れます）。

DECLARE @Chunk int = 64750;

SELECT
  t.Id,
  n.PartId,
  SUBSTRING(t.Comments, (n.PartId - 1) * @Chunk + 1, @Chunk) AS CommentPart
FROM dbo.YourTable AS t
CROSS APPLY (
  SELECT TOP (
           CASE
             WHEN t.Comments IS NULL THEN 0
             ELSE (CONVERT(bigint, LEN(t.Comments)) + @Chunk - 1) / @Chunk
           END
         )
         ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) AS PartId
  FROM sys.all_objects AS a
  CROSS JOIN sys.all_objects AS b
) AS n
WHERE t.Id = 1
ORDER BY n.PartId;

この方式のメリットは次のとおりです。

  • 複数行でも同じ形で書ける：WHERE t.Id=1 を外せば全行を一括で分割できます。
  • MAXRECURSION を気にしなくていい：分割数が 100 を超えても問題になりません。
  • 実行計画が読みやすい：大規模データでもボトルネックが見つけやすいです。

一方で、生成元（ここでは sys.all_objects a × sys.all_objects b）が分割数に足りないと途中で止まります。チャンクを極端に小さくしたり、1件あたりのコメントが極端に長い場合は、クロス結合を増やすか、専用の数表を作成するのが安全です。

分割した結果を別テーブルに保存する例（再結合できる設計）

分割したパーツを永続化して、あとで結合・検索・移行に使いたい場合は「親ID + PartId」の複合キーを持つテーブルを作ると扱いやすくなります。パーツ列は 64,750 文字を超える可能性があるなら varchar(max) を選び、要件どおり固定長に必ず収まるなら（外部仕様が本当に “文字数” で保証されるなら）最小限の型にするのが基本です。

CREATE TABLE dbo.CommentParts
(
  Id        int          NOT NULL,
  PartId    int          NOT NULL,
  PartText  varchar(max) NOT NULL,
  CONSTRAINT PK_CommentParts PRIMARY KEY (Id, PartId)
);

挿入は先ほどの数表方式をそのまま使えます。

DECLARE @Chunk int = 64750;

INSERT INTO dbo.CommentParts(Id, PartId, PartText)
SELECT
  t.Id,
  n.PartId,
  SUBSTRING(t.Comments, (n.PartId - 1) * @Chunk + 1, @Chunk) AS PartText
FROM dbo.YourTable AS t
CROSS APPLY (
  SELECT TOP (
           CASE
             WHEN t.Comments IS NULL THEN 0
             ELSE (CONVERT(bigint, LEN(t.Comments)) + @Chunk - 1) / @Chunk
           END
         )
         ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) AS PartId
  FROM sys.all_objects AS a
  CROSS JOIN sys.all_objects AS b
) AS n;

分割後に元へ戻したいときは、PartId 順に連結します（SQL Server 2017 以降なら STRING_AGG が便利です）。

-- SQL Server 2017+ の例：分割結果を結合して戻す
SELECT
  Id,
  STRING_AGG(PartText, '') WITHIN GROUP (ORDER BY PartId) AS RebuiltComments
FROM dbo.CommentParts
GROUP BY Id;

方式比較：再帰CTEと数表（Tally）はどちらを選ぶべきか

方式良い点向いているケース注意点
再帰CTESQLが短く、発想が直感的。1件ずつ追いやすい対象が少数、分割数が小さい（例：数パーツ〜数十パーツ）デフォルト100階層。大量行では遅くなりやすい
数表（Tally）set-basedで安定。全行一括処理が得意バッチ処理、移行、夜間ジョブ、数万行以上十分な連番生成が必要。実装は最初に固める
GENERATE_SERIES（SQL Server 2022+）連番生成が読みやすく、コード量が減る新しめ環境でシンプルに書きたいバージョン要件に注意

落とし穴：LEN と DATALENGTH、そして「64,750 の根拠」がバイト制限の場合

ここまでの例は「64,750 文字で分割」という前提で LEN（文字数）ベースで説明しました。ただし、要件によっては「64KB（バイト）制限を避けたい」という事情で 64,750 という数値が選ばれていることがあります。

  • varchar は環境（照合順序・コードページ）によって 1文字が 1〜2バイトになり得ます。日本語を含むテキストだと DATALENGTH（バイト数）が想定より増えます。
  • nvarchar は基本的に 1文字=2バイト（UTF-16）なので、バイト制限が根拠なら「文字数」で切ると簡単に超過します。

外部仕様が「バイト上限」なら、単純な SUBSTRING だけで安全に切るのは難しくなります（バイト境界で切ると文字化けする可能性があるため）。この場合は次のどちらかを検討すると事故が減ります。

  • 外部の制限が「文字数」なのか「バイト数」なのかを仕様として確定させる（ここが曖昧なまま実装すると必ず後で揉めます）
  • バイト上限なら、受け側の文字コード前提に合わせた変換・検証を入れる（できればアプリ層でエンコード後のサイズ確認をする）

まとめ：PartId=1 しか出ない問題は「データ長」と「型」を疑うのが最優先

  • まず LEN/DATALENGTH で 本当に長文が入っているか を確認する
  • テストデータ生成は REPLICATE(CAST(... AS varchar(max)), ...) のように 入力型を max に寄せる（後からCASTしても手遅れ）
  • 分割ロジックは SUBSTRING と開始位置の計算が基本。少数なら再帰CTE、大量なら数表（Tally）が安定
  • 分割結果を保存するなら (Id, PartId) をキーにし、再結合できる形にしておく

この流れで確認すれば、「分割クエリは正しいのに PartId=1 の 1行しか出ない」というケースはスムーズに切り分けできます。まずは長さチェックから始めて、必要に応じて再帰CTEか数表方式へ移行していくのがおすすめです。

Microsoft
SQL Server varchar(max) REPLICATE 再帰CTE 数表

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次