如何從 SQL 中的全名字段中高效解析名字、中間名和姓氏？

從SQL 中的全名解析名字、中間名和姓氏

問題：

我們如何從SQL 中的單一全名字段中提取名字、中間名和姓氏？這對於匹配全名不完全匹配的名稱非常有用。

解決方案：

讓我們考慮以下提供 90% 準確率的實用解決方案：

WITH FIRST_NAME AS (
  SELECT
    TITLE.ORIGINAL_INPUT_DATA,
    TITLE.TITLE,
    CASE
      WHEN 0 = CHARINDEX(' ', TITLE.REST_OF_NAME) THEN TITLE.REST_OF_NAME
      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 more spaces? assume rest is the last name
      ELSE SUBSTRING(TITLE.REST_OF_NAME, 1 + CHARINDEX(' ', TITLE.REST_OF_NAME), LEN(TITLE.REST_OF_NAME))
    END AS REST_OF_NAME
  FROM
    (
      SELECT
        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,
        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
            REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)), ' ', ' '), ' ', ' ') AS FULL_NAME,
            FULL_NAME AS ORIGINAL_INPUT_DATA
          FROM
            (
              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
)
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 the 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
  FIRST_NAME;

此解決方案處理以下特殊情況情況：

• NULL、前導/尾隨空格、連續空格
• 沒有中間名
• 原始全名作為單獨的列
• 具體列表前綴作為單獨的「標題」列

以上是如何從 SQL 中的全名字段中高效解析名字、中間名和姓氏？的詳細內容。更多資訊請關注PHP中文網其他相關文章！