SQL Server：CHARINDEX、STUFF、STRING_AGG関数で文字列操作をマスター

2024-06-18

SQL Server で文字列内の n 番目の出現を検索する方法

方法 1: CHARINDEX 関数と LEN 関数

この方法は、最もシンプルでわかりやすい方法です。

SELECT
  CHARINDEX('needle', 'haystack', n) AS position
FROM
  your_table;

このクエリは、'haystack' 文字列内の 'needle' 文字列の n 番目の出現位置を返します。

例:

SELECT
  CHARINDEX('a', 'apple', 2) AS position;

利点:

シンプルでわかりやすい
読みやすい

パフォーマンスが遅い
大規模な文字列には非効率的

方法 2: STUFF 関数

この方法は、CHARINDEX 関数と LEN 関数よりも高速で効率的です。

SELECT
  SUBSTRING('haystack', CHARINDEX('needle', 'haystack', n), LEN('needle')) AS substring
FROM
  your_table;

SELECT
  SUBSTRING('apple', CHARINDEX('a', 'apple', 2), LEN('a')) AS substring;

方法 3: STRING_AGG 関数

この方法は、SQL Server 2016 以降で使用できます。

SELECT
  STRING_AGG(substring FROM (
    SELECT
      SUBSTRING('haystack', CHARINDEX('needle', 'haystack', row_number() OVER (ORDER BY position)), LEN('needle')) AS substring,
      ROW_NUMBER() OVER (ORDER BY position) AS position
    FROM
      (
        SELECT
          CHARINDEX('needle', 'haystack') AS position
        FROM
          your_table
      ) AS positions
  ) AS subquery
  ORDER BY position
  FOR 1 OFFSET n-1 ROWS) AS substring
FROM
  your_table;

SELECT
  STRING_AGG(substring FROM (
    SELECT
      SUBSTRING('apple', CHARINDEX('a', 'apple', row_number() OVER (ORDER BY position)), LEN('a')) AS substring,
      ROW_NUMBER() OVER (ORDER BY position) AS position
    FROM
      (
        SELECT
          CHARINDEX('a', 'apple') AS position
        FROM
          your_table
      ) AS positions
  ) AS subquery
  ORDER BY position
  FOR 1 OFFSET 2-1 ROWS) AS substring;

STRING_AGG 関数は、複数の出現部分を連結できる

最も複雑な方法

状況に応じて適切な方法を選択してください。

シンプルでわかりやすい方法が必要な場合は、CHARINDEX 関数と LEN 関数を使用します。
パフォーマンスが重要の場合は、STUFF 関数を使用します。
SQL Server 2016 以降を使用しており、複数の出現部分を連結する必要がある場合は、STRING_AGG 関数を使用します。

補足:

上記の例はすべて、'needle' 文字列が 'haystack' 文字列内に存在することを前提としています。'needle' 文字列が存在しない場合は、エラーが発生します。
大文字と小文字を区別する必要がある場合は、COLLATE clause を使用できます。

方法 1: CHARINDEX 関数と LEN 関数

-- サンプルテーブルを作成
CREATE TABLE your_table (
  id INT IDENTITY PRIMARY KEY,
  text VARCHAR(255)
);

-- サンプルデータを挿入
INSERT INTO your_table (text)
VALUES
  ('The quick brown fox jumps over the lazy dog'),
  ('The cat sat on the mat'),
  ('Mary had a little lamb');

-- 'haystack' 文字列内の 'needle' 文字列の 2 番目の出現位置を検索
SELECT
  id,
  text,
  CHARINDEX('a', text, 2) AS position
FROM
  your_table;

このクエリは次の結果を返します。

id | text                                | position
---+--------------------------------------+---------
1  | The quick brown fox jumps over the lazy dog | 9
2  | The cat sat on the mat                   | 5
3  | Mary had a little lamb                   | 18

方法 2: STUFF 関数

-- サンプルテーブルを作成 (上記と同じ)
-- サンプルデータを挿入 (上記と同じ)

-- 'haystack' 文字列内の 'needle' 文字列の 2 番目の出現部分を検索
SELECT
  id,
  text,
  SUBSTRING(text, CHARINDEX('a', text, 2), LEN('a')) AS substring
FROM
  your_table;

id | text                                | substring
---+--------------------------------------+---------
1  | The quick brown fox jumps over the lazy dog | a
2  | The cat sat on the mat                   | a
3  | Mary had a little lamb                   | a

方法 3: STRING_AGG 関数

-- サンプルテーブルを作成 (上記と同じ)
-- サンプルデータを挿入 (上記と同じ)

-- SQL Server 2016 以降のみ使用可能

-- 'haystack' 文字列内の 'needle' 文字列の 2 番目の出現部分を検索
SELECT
  id,
  text,
  STRING_AGG(substring FROM (
    SELECT
      SUBSTRING(text, CHARINDEX('a', text, row_number() OVER (ORDER BY position)), LEN('a')) AS substring,
      ROW_NUMBER() OVER (ORDER BY position) AS position
    FROM
      (
        SELECT
          CHARINDEX('a', text) AS position
        FROM
          your_table
      ) AS positions
  ) AS subquery
  ORDER BY position
  FOR 1 OFFSET 2-1 ROWS) AS substring
FROM
  your_table;

id | text                                | substring
---+--------------------------------------+---------
1  | The quick brown fox jumps over the lazy dog | a
2  | The cat sat on the mat                   | a
3  | Mary had a little lamb                   | a

SQL Server で文字列内の n 番目の出現を検索するその他の方法

REPEAT 関数と SUBSTRING 関数

SELECT
  id,
  text,
  SUBSTRING(
    text,
    CHARINDEX('needle', text, 1) + (n - 1) * LEN('needle'),
    LEN('needle')
  ) AS substring
FROM
  your_table;

XML データ型

この方法は、XML データ型を使用して文字列を処理することで、n 番目の出現を検索します。

SELECT
  id,
  text,
  CAST(
    '<root>' + REPLACE(text, 'needle', '</needle><needle>') + '</root>'
    AS XML
  ).value('//needle[n]', 'VARCHAR(255)') AS substring
FROM
  your_table;

T-SQL クエリ内の再帰

この方法は、T-SQL クエリ内で再帰を使用して、n 番目の出現を検索します。

DECLARE @text VARCHAR(255);
DECLARE @needle VARCHAR(255);
DECLARE @n INT;
DECLARE @position INT;
DECLARE @substring VARCHAR(255);

SET @text = 'The quick brown fox jumps over the lazy dog';
SET @needle = 'a';
SET @n = 2;
SET @position = 0;
SET @substring = '';

WHILE @position < @n
BEGIN
  SET @position = CHARINDEX(@needle, @text, @position + 1);

  IF @position = 0
  BEGIN
    BREAK;
  END;

  SET @substring = @substring + SUBSTRING(@text, @position, LEN(@needle));
  SET @text = SUBSTRING(@text, @position + LEN(@needle));
END;

SELECT @substring AS substring;

CLR メソッド

CREATE ASSEMBLY MyAssembly
FROM 'MyAssembly.dll'
USING MyNamespace;

CREATE FUNCTION FindNthOccurrence(@text NVARCHAR(4000), @needle NVARCHAR(400), @n INT)
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME MyAssembly.MyNamespace.FindNthOccurrence;

SELECT
  id,
  text,
  FindNthOccurrence(text, 'needle', 2) AS substring
FROM
  your_table;