如何從 SQL 中的全名字段解析名字、中間名和姓氏？-mysql教程-PHP中文網

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

Patricia Arquette

發布： 2024-12-26 19:58:14

原創

507 人瀏覽過

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

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

從單一全名字段中提取單一名稱元件可以是資料處理中的常見任務。讓我們探索一個實用的 SQL 解決方案來完成此任務。

解：

1.取名字：

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

登入後複製

2。提取中間名（可選）：

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.提取姓氏：

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

登入後複製

4.針對特殊情況進行拆解分：

NULL名稱
前導/尾隨空格
多個連續空格
只存在名字
保留原始全名
前綴和標題

具有特殊情況處理的擴展查詢：

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

登入後複製

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