SQL: PATINDEX() 関数と TRY_CONVERT() 関数を使って氏名フィールドから苗字、名前、中間名を抽出する

2024-04-07

SQL: 氏名フィールドから苗字、名前、中間名を抽出する

使用する環境

SQL Server
Transact-SQL (T-SQL)

前提条件

テーブルCustomers with a fullname field

解決策

以下の3つの方法で、fullnameフィールドから苗字、名前、中間名を抽出できます。

SUBSTRING()関数は、文字列から部分文字列を抽出するために使用できます。以下の例では、fullnameフィールドから苗字、名前、中間名を抽出するために3つのSUBSTRING()関数を組み合わせて使用しています。

SELECT
    SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) AS LastName,
    SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, CHARINDEX(' ', fullname, CHARINDEX(' ', fullname) + 1) - 1) AS FirstName,
    SUBSTRING(fullname, CHARINDEX(' ', fullname, CHARINDEX(' ', fullname) + 1) + 1) AS MiddleName
FROM Customers;

PATINDEX() 関数と TRY_CONVERT() 関数を使用する

PATINDEX()関数は、文字列内のパターンを見つけるために使用できます。以下の例では、PATINDEX()関数を使用して苗字、名前、中間名の位置を見つけ、TRY_CONVERT()関数を使用してそれぞれの部分文字列を抽出しています。

SELECT
    TRY_CONVERT(VARCHAR(50), SUBSTRING(fullname, 1, PATINDEX('%[ ]%([a-zA-Z]+)[ ]%', fullname) - 1)) AS LastName,
    TRY_CONVERT(VARCHAR(50), SUBSTRING(fullname, PATINDEX('%[ ]%([a-zA-Z]+)[ ]%', fullname) + 1, PATINDEX('%[ ]%([a-zA-Z]+)[ ]%', fullname, PATINDEX('%[ ]%([a-zA-Z]+)[ ]%', fullname) + 1) - 1)) AS FirstName,
    TRY_CONVERT(VARCHAR(50), SUBSTRING(fullname, PATINDEX('%[ ]%([a-zA-Z]+)[ ]%', fullname, PATINDEX('%[ ]%([a-zA-Z]+)[ ]%', fullname) + 1) + 1)) AS MiddleName
FROM Customers;

CASE WHENステートメントを使用して、条件に基づいて異なる値を抽出できます。以下の例では、CASE WHENステートメントを使用してfullnameフィールドを解析し、苗字、名前、中間名を抽出しています。

SELECT
    CASE WHEN CHARINDEX(' ', fullname) > 0 THEN SUBSTRING(fullname, 1, CHARINDEX(' ', fullname) - 1) END AS LastName,
    CASE WHEN CHARINDEX(' ', fullname, CHARINDEX(' ', fullname) + 1) > 0 THEN SUBSTRING(fullname, CHARINDEX(' ', fullname) + 1, CHARINDEX(' ', fullname, CHARINDEX(' ', fullname) + 1) - 1) END AS FirstName,
    CASE WHEN CHARINDEX(' ', fullname, CHARINDEX(' ', fullname, CHARINDEX(' ', fullname) + 1) + 1) > 0 THEN SUBSTRING(fullname, CHARINDEX(' ', fullname, CHARINDEX(' ', fullname, CHARINDEX(' ', fullname) + 1) + 1) + 1) END AS MiddleName
FROM Customers;

上記の3つの方法のいずれかを使用して、SQL ServerのT-SQLを使用してfullnameフィールドから苗字、名前、中間名を抽出することができます。

-- テーブル Customers

CREATE TABLE Customers (
    CustomerID int NOT NULL IDENTITY(1, 1),
    fullname varchar(50) NOT NULL
);

-- データ挿入

INSERT INTO Customers (fullname) VALUES ('山田 太郎, 田中 花子, 佐藤 健太郎');

-- 苗字、名前、中間名を抽出

SELECT
    SUBSTRING(fullname, 1, CHARINDEX(',', fullname) - 1) AS LastName,
    SUBSTRING(fullname, CHARINDEX(',', fullname) + 1, CHARINDEX(',', fullname, CHARINDEX(',', fullname) + 1) - 1) AS FirstName,
    SUBSTRING(fullname, CHARINDEX(',', fullname, CHARINDEX(',', fullname) + 1) + 1) AS MiddleName
FROM Customers;

このコードを実行すると、以下の結果が出力されます。

LastName | FirstName | MiddleName
------- | -------- | --------
山田    | 太郎      | 
田中    | 花子      | 
佐藤    | 健太郎   |

上記のコード以外にも、SPLIT()関数やPARSENAME()関数を使用して苗字、名前、中間名を抽出することができます。

他の方法

SPLIT() 関数を使用する

SPLIT()関数は、文字列を区切り文字で分割して、文字列の配列を返すことができます。以下の例では、SPLIT()関数を使用してfullnameフィールドをカンマで分割し、苗字、名前、中間名を抽出しています。

SELECT
    s.value AS LastName,
    t.value AS FirstName,
    u.value AS MiddleName
FROM Customers
CROSS APPLY STRING_SPLIT(fullname, ',') AS s
CROSS APPLY STRING_SPLIT(s.value, ' ') AS t
CROSS APPLY STRING_SPLIT(t.value, ' ') AS u;

PARSENAME()関数は、文字列から苗字、名前、中間名、接尾辞を抽出するために使用できます。以下の例では、PARSENAME()関数を使用してfullnameフィールドから苗字、名前、中間名を抽出しています。

SELECT
    PARSENAME(fullname, 1) AS LastName,
    PARSENAME(fullname, 2) AS FirstName,
    PARSENAME(fullname, 4) AS MiddleName
FROM Customers;

レギュラー式を使用する

REGEX_EXTRACT() 関数を使用して、正規表現を使用してfullnameフィールドから苗字、名前、中間名を抽出することができます。以下の例では、正規表現を使用してfullnameフィールドから苗字、名前、中間名を抽出しています。

SELECT
    REGEX_EXTRACT(fullname, '^([^\s]+)\s+([^\s]+)\s+([^\s]+)$', 1) AS LastName,
    REGEX_EXTRACT(fullname, '^([^\s]+)\s+([^\s]+)\s+([^\s]+)$', 2) AS FirstName,
    REGEX_EXTRACT(fullname, '^([^\s]+)\s+([^\s]+)\s+([^\s]+)$', 3) AS MiddleName
FROM Customers;

上記の3つの方法は、それぞれ異なる方法でfullnameフィールドから苗字、名前、中間名を抽出する方法を示しています。どの方法を使用するかは、要件と環境によって異なります。

sql sql-server t-sql