SQL の単一の「フルネーム」フィールドから名、ミドルネーム、姓を効率的に解析し、さまざまなデータの不一致や特殊なケースを処理するにはどうすればよいでしょうか?

SQL を使用してフルネームフィールドから名、ミドルネーム、姓を解析する

データを処理するとき、多くの場合、名前をそれぞれの名前に分割する必要があります。操作を容易にする構成部品。この場合、一般的なデータの変動を考慮しながら、「フルネーム」フィールドから名、ミドルネーム、姓を抽出する必要があります。

精度 90% の効率的なソリューション

提供された例は、ほとんどのケースを高い精度で処理する実用的なソリューションを提供します。

SELECT
  FIRST_NAME.ORIGINAL_INPUT_DATA,
  FIRST_NAME.TITLE,
  FIRST_NAME.FIRST_NAME,
  CASE
    WHEN 0 = CHARINDEX(' ', FIRST_NAME.REST_OF_NAME)
    THEN NULL  -- No more spaces? Assume rest is last name
    ELSE SUBSTRING(FIRST_NAME.REST_OF_NAME, 1, CHARINDEX(' ', FIRST_NAME.REST_OF_NAME) - 1)
  END AS MIDDLE_NAME,
  SUBSTRING(FIRST_NAME.REST_OF_NAME, 1 + CHARINDEX(' ', FIRST_NAME.REST_OF_NAME), LEN(FIRST_NAME.REST_OF_NAME)) AS LAST_NAME
FROM
  (
    SELECT
      TITLE.TITLE,
      CASE
        WHEN 0 = CHARINDEX(' ', TITLE.REST_OF_NAME)
        THEN TITLE.REST_OF_NAME -- No space? Return the whole thing
        ELSE SUBSTRING(TITLE.REST_OF_NAME, 1, CHARINDEX(' ', TITLE.REST_OF_NAME) - 1)
      END AS FIRST_NAME,
      CASE
        WHEN 0 = CHARINDEX(' ', TITLE.REST_OF_NAME)
        THEN NULL  -- No spaces at all? Then 1st name is all we have
        ELSE SUBSTRING(TITLE.REST_OF_NAME, CHARINDEX(' ', TITLE.REST_OF_NAME) + 1, LEN(TITLE.REST_OF_NAME))
      END AS REST_OF_NAME,
      TITLE.ORIGINAL_INPUT_DATA
    FROM
      (
        SELECT
          -- If the first three characters are in this list,
          -- then pull it as a "title". Otherwise return NULL for title.
          CASE
            WHEN SUBSTRING(TEST_DATA.FULL_NAME, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS')
            THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME, 1, 3)))
            ELSE NULL
          END AS TITLE,
          -- If you change the list, don't forget to change it here, too.
          CASE
            WHEN SUBSTRING(TEST_DATA.FULL_NAME, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS')
            THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME, 4, LEN(TEST_DATA.FULL_NAME))))
            ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
          END AS REST_OF_NAME,
          TEST_DATA.ORIGINAL_INPUT_DATA
        FROM
          (
            SELECT
              -- Trim leading & trailing spaces before trying to process
              -- Disallow extra spaces *within* the name
              REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)), '  ', ' '), '  ', ' ') AS FULL_NAME,
              FULL_NAME AS ORIGINAL_INPUT_DATA
            FROM
              (
                -- Replace this block with your actual table
                SELECT 'GEORGE W BUSH' AS FULL_NAME
                UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
                UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
                UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
                UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
                UNION SELECT 'TOMMY' AS FULL_NAME
                UNION SELECT 'BILLY' AS FULL_NAME
              ) RAW_DATA
          ) TEST_DATA
      ) TITLE
  ) FIRST_NAME;

これクエリは、「MR」、「MS」、「DR」、「MRS」などのプレフィックスを別の「TITLE」列として識別して削除し、欠落している名前、名前内の複数のスペース、および単一部分の「フルネーム」（最初の）を処理します。

特殊なケースの処理

このソリューションには、特定のケースに対処する修正も含まれています空の「フルネーム」フィールド、末尾/先頭のスペース、複数の連続するスペース、名のみを含む「フルネーム」などの特殊なケース:

-- Handle the following special cases:
-- 1 - The NAME field is NULL
-- 2 - The NAME field contains leading / trailing spaces
-- 3 - The NAME field has > 1 consecutive space within the name
-- 4 - The NAME field contains ONLY the first name
-- 5 - Include the original full name in the final output as a separate column, for readability
-- 6 - Handle a specific list of prefixes as a separate "title" column

SELECT
  FIRST_NAME.ORIGINAL_INPUT_DATA,
  FIRST_NAME.TITLE,
  FIRST_NAME.FIRST_NAME,
  CASE
    WHEN 0 = CHARINDEX(' ', FIRST_NAME.REST_OF_NAME)
    THEN NULL  -- No more spaces? Assume rest is last name
    ELSE SUBSTRING(FIRST_NAME.REST_OF_NAME, 1, CHARINDEX(' ', FIRST_NAME.REST_OF_NAME) - 1)
  END AS MIDDLE_NAME,
  SUBSTRING(FIRST_NAME.REST_OF_NAME, 1 + CHARINDEX(' ', FIRST_NAME.REST_OF_NAME), LEN(FIRST_NAME.REST_OF_NAME)) AS LAST_NAME
FROM
  (
    SELECT
      TITLE.TITLE,
      CASE
        WHEN 0 = CHARINDEX(' ', TITLE.REST_OF_NAME)
        THEN TITLE.REST_OF_NAME -- No space? Return the whole thing
        ELSE SUBSTRING(TITLE.REST_OF_NAME, 1, CHARINDEX(' ', TITLE.REST_OF_NAME) - 1)
      END AS FIRST_NAME,
      CASE
        WHEN 0 = CHARINDEX(' ', TITLE.REST_OF_NAME)
        THEN NULL  -- No spaces at all? Then 1st name is all we have
        ELSE SUBSTRING(TITLE.REST_OF_NAME, CHARINDEX(' ', TITLE.REST_OF_NAME) + 1, LEN(TITLE.REST_OF_NAME))
      END AS REST_OF_NAME,
      TITLE.ORIGINAL_INPUT_DATA
    FROM
      (
        SELECT
          -- If the first three characters are in this list,
          -- then pull it as a "title". Otherwise return NULL for title.
          CASE
            WHEN SUBSTRING(TEST_DATA.FULL_NAME, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS')
            THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME, 1, 3)))
            ELSE NULL
          END AS TITLE,
          -- If you change the list, don't forget to change it here, too.
          CASE
            WHEN SUBSTRING(TEST_DATA.FULL_NAME, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS')
            THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME, 4, LEN(TEST_DATA.FULL_NAME))))
            ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
          END AS REST_OF_NAME,
          TEST_DATA.ORIGINAL_INPUT_DATA
        FROM
          (
            SELECT
              -- Trim leading & trailing spaces before trying to process
              -- Disallow extra spaces *within* the name
              REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)), '  ', ' '), '  ', ' ') AS FULL_NAME,
              FULL_NAME AS ORIGINAL_INPUT_DATA
            FROM
              (
                -- Replace this block with your actual table
                SELECT 'GEORGE W BUSH' AS FULL_NAME
                UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
                UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
                UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
                UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
                UNION SELECT 'TOMMY' AS FULL_NAME
                UNION SELECT 'BILLY' AS FULL_NAME
                UNION SELECT NULL AS FULL_NAME
                UNION SELECT ' ' AS FULL_NAME
                UNION SELECT '    JOHN  JACOB     SMITH' AS FULL_NAME
                UNION SELECT ' DR  SANJAY       GUPTA' AS FULL_NAME
                UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
                UNION SELECT ' MRS  SUSAN ADAMS' AS FULL_NAME
                UNION SELECT ' MS AUGUSTA  ADA   KING ' AS FULL_NAME      
              ) RAW_DATA
          ) TEST_DATA
      ) TITLE
  ) FIRST_NAME;

以上がSQL の単一の「フルネーム」フィールドから名、ミドルネーム、姓を効率的に解析し、さまざまなデータの不一致や特殊なケースを処理するにはどうすればよいでしょうか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。