다양한 데이터 불일치 및 특수 사례를 처리하면서 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' 열로 식별 및 제거하고 누락된 이름, 이름의 여러 공백 및 단일 부분 'fullname'(첫 번째)을 처리합니다. 이름만).

특수 사례 처리

이 솔루션에는 빈 항목과 같은 특정 특수 사례를 해결하는 수정 사항도 포함되어 있습니다. 'fullname' 필드, 후행/선행 공백, 여러 개의 연속 공백 및 이름만 포함하는 'fullname':

-- 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!