How can I parse first, middle, and last names from a full name field in SQL?

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

Extracting individual name components from a single fullname field can be a common task in data processing. Let's explore a practical SQL solution to accomplish this.

Solution:

1. Extract First Name:

SUBSTRING(FULL_NAME, 1, CHARINDEX(' ', FULL_NAME)-1)

2. Extract Middle Name (Optional):

CASE WHEN 0 = CHARINDEX(' ', REST_OF_NAME)
     THEN NULL  -- No more spaces? Assume rest is the last name
     ELSE SUBSTRING(REST_OF_NAME, 1, CHARINDEX(' ', REST_OF_NAME)-1)
     END

3. Extract Last Name:

SUBSTRING(REST_OF_NAME, 1 + CHARINDEX(' ', REST_OF_NAME), LEN(REST_OF_NAME))

4. Split for Special Cases:

• NULL names
• Leading/trailing spaces
• Multiple consecutive spaces
• Only first name present
• Original fullname retention
• Prefixes and titles

Expanded Query with Special Case Handling:

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
  (
  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 @ 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
      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  -- Not in the title list? Return 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
        YOUR_TABLE  -- Replace this with your actual table
      ) TEST_DATA
    ) TITLE
  ) FIRST_NAME

The above is the detailed content of How can I 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!