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

Question

我有一个表，其中包含这样的用户名。

Name
-----
Smith-Bay, Michael R.
Abbott, David Jr.
Actor, Cody
Agular, Stephen V.

我需要这个名字看起来像：

Last         First    MI
-------------------------
Smith-Bay    Michael  R
Abbott       David    Jr
Actor        Cody
Agular       Stephen  V 

我有以下 SQL 将名称拆分为第一个和最后一个：

select vl.lastname, vf.firstname
from users as t cross apply
(values (left(t.name, charindex(', ', t.name)), stuff(t.name, 1, 
charindex(', ', t.name) + 1, ''))) vl(lastname, rest) 
cross apply 
(values (left(vl.rest, charindex(' ', vl.rest + ' ')))) vf(firstname)
order by  vl.lastname

我如何应用另一个交叉应用来提取名字之后的所有内容减去最后的句点？

标签： sqlsql-servertsql

Answer 1

我不得不在很多情况下这样做，因为我定期进行 ETL 工作，并且由于数据存储错误或者只是需要从报告中提取数据而需要从字符串中提取项目。数据并不总是很好地打包在单独的列中，我发现自己出于各种原因解析数据。希望您解析的数据是一致的。不一致的数据要么使这变得更加困难或不可能。如果您可以依靠您的姓名完全符合您建议的格式，那么我下面的方法将完美运行。我已经用过很多次了。

下面的方法我在许多不同的语言中使用过。我已经在 MS ACCESS、Microsoft SSMS 和 C# 中完成了这项工作。我的示例来自 Oracle。

基本思想是：

Find the character positions分隔您的 First_Name、Last_Name 和 Middle_Initial 字符串。

Extract Strings into New Columns使用获得的字符位置。

代码如下：

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 语句中完成。

基本上，这证明除了一些简单的字符串解析函数之外，您不需要任何额外的东西。您所需要的只是 Instring 和 Substring，它们通常以任何语言提供。没有存储过程，没有临时表，也不需要额外的外部代码。除非有原始问题范围之外的其他因素使得有必要使用除 SQL 之外的任何东西。