How to Efficiently Parse First, Middle, and Last Names from a Full Name Field in SQL?

Parsing First, Middle, and Last Names from a Full Name in SQL

Problem:

How can we extract the first, middle, and last names from a single fullname field in SQL? This is useful for matching names that are not exact matches on fullname.

Solution:

Let's consider the following practical solution that provides 90% accuracy:

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;

This solution handles the following special cases:

• NULL, leading/trailing spaces, consecutive spaces
• No middle name
• Original fullname as a separate column
• Specific list of prefixes as a separate "title" column

The above is the detailed content of How to Efficiently Parse First, Middle, and Last Names from a Full Name Field in SQL?. For more information, please follow other related articles on the PHP Chinese website!