如何在 T-SQL 中用名稱中的空格分隔名稱字元串

我有一個名稱列表，例如Doe John James：Doe Jane和Doe Henry Albert Sr

我需要將它們分成名稱的四個組成部分（如果名稱有四個部分），將它們中的每一個放在自己的列中（姓氏、名字、中間名、後綴），同時允許名稱可能或者可能沒有中間名或後綴。

除了有後綴的第三個實例之外，我已經弄清楚了所有內容。

對於名字，我有：

case
   when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
       then LEFT(SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 1, LEN(LTRIM(RTRIM(@name)))), CHARINDEX(' ', SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 2, LEN(LTRIM(RTRIM(@name))))))
   else
       SUBSTRING(@name, CHARINDEX(' ', @name) + 1, LEN(@name))
end as firstName

這很瘋狂，但它確實有效……

對於中間名，我有：

case
   when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
       then
           REVERSE(SUBSTRING(REVERSE(LTRIM(RTRIM(@name))),1,CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@name))))))
   else ''
end as middleName

不像名字那麼複雜。

由於姓氏在開頭，這是最簡單的：

SUBSTRING(LTRIM(RTRIM(@name)),1,CHARINDEX(' ',LTRIM(RTRIM(@name)))-1) as lastName

但我不知道如何允許帶有後綴的名稱（Jr、Sr、III 等）。

這樣的事情對您有幫助嗎：

With split(id, n, start, pos) as(
   SELECT id, 1, 0, CHARINDEX(' ', fullname) FROM @names
   UNION ALL
   SELECT n.id, n+1, pos+1, CHARINDEX(' ', fullname, pos+1) 
   FROM @names n
   INNER JOIN split s ON n.id = s.id
   WHERE CHARINDEX(' ', fullname, start+1) > 0
)
SELECT id
   , lastName = MAX(CASE WHEN v.n = 1 THEN v.val END)
   , firstname = MAX(CASE WHEN v.n = 2 THEN v.val END)
   , middleName = MAX(CASE WHEN v.n = 3 THEN v.val END)
   , title = MAX(CASE WHEN v.n = 4 THEN v.val END)
FROM (
   SELECT n.id, s.n
       , SUBSTRING(n.fullname
               , s.start
               , CASE WHEN s.pos = 0 THEN LEN(n.fullname)+1 ELSE s.pos END -s.start
           )
   FROM @names n
   INNER JOIN split s ON n.id = s.id
) as v(id, n, val)
GROUP BY id
--OPTION(MAXRECURSION  4)
;

• 遞歸 CTE 獲取每個名稱的開始和結束位置以及n從 1 到最大 4 的值。
• 根據nGROUP BY 的值將它們放在各自的列中。

在一張大桌子上，它可能不是很有效。

輸出：

id  lastName    firstname   middleName  title
0   Doe         John        James       NULL
1   Doe         Jane        NULL        NULL
2   Doe         Henry       Albert      Sr
3   Bart        Simpson     NULL        NULL

數據：

declare @names table(id int identity(0, 1), fullname varchar(200), first varchar(50), middle varchar(50), last varchar(50), title varchar(50));
INSERT INTO @names(fullname) values
   ('Doe John James')
   , ('Doe Jane')
   , ('Doe Henry Albert Sr')
   , ('Bart Simpson')
;

您可能希望首先拆分單詞，然後應用您的邏輯來分隔名稱。您可以訪問以下連結以獲取自定義拆分功能：

T SQL 表值函式以逗號分隔列

引用自：https://dba.stackexchange.com/questions/106228