SQL:交叉应用将名称拆分为名字、姓氏和 MI

The method below I've used in many different languages. I've done this in MS ACCESS, Microsoft SSMS and C#. My example is out of Oracle.

基本思想是:

找到分隔你的 First_Name、Last_Name 和 Middle_Initial 字符串的字符位置.

使用获得的字符位置将字符串提取到新列中.

代码如下:

WITH character_pos AS
(
/* First we need the character positions for spaces, commas and the period for the middle initial */
SELECT name
  /* Find 1st Space in the name so we can extract the first name from the string */
  , instr(name, ', ') AS comma_1st_space_pos
  /* Find 2nd Space in the name so we can extract the last name from the string */
  , instr(name, ' ', 1, 2) AS comma_2nd_space_pos
  /* Get the Length of the last name so we know how many characters the substr function should extract */
  , instr(name, ' ', 1, 2) - (instr(name, ', ') + 2) AS last_name_length
  /* Find period in the name so we can extract the Middle Initial should it exist */
  , instr(name, '.')  AS period_pos
  , (instr(name, '.') - 1) - instr(name, ' ', 1, 2) AS middle_initial_length
  
FROM parse_name
) /* END character_pos CTE */

SELECT name  
  , substr(name, 0, comma_1st_space_pos -1) AS last_name
   
  , CASE WHEN  period_pos = 0 THEN substr(name, comma_1st_space_pos + 2)
    ELSE substr(name, comma_1st_space_pos + 2, last_name_length) 
    END AS first_name
   
  , substr(name, comma_2nd_space_pos + 1, middle_initial_length) AS middle_initial
  
  , comma_1st_space_pos, comma_2nd_space_pos, last_name_length
  , period_pos, middle_initial_length
FROM character_pos
;

我使用 CTE 只是为了在实际提取之外组织字符位置，但这一切都可以在一个 SQL 语句中完成.