如何从 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中文网其他相关文章！