删除全名中间的首字母缩写，如(A或H.)

Question

如何从全名中删除中间的首字母？通过创建自定义函数？

我想将FullName转换为FullNameWithoutInitials:

FullName	FullNameWithoutInitials
John A Smith	John Smith
John B.Smith	John Smith
John Smith C	John Smith
John Smith Z./td>	John Smith

请尝试以下解决方案。

显然，它不能处理所有可能的带有名称的场景。

它使用XML和XQuery标记FullName列。

XPath谓词[string-length() gt 2]正在删除任何小于或等于2个字符的单词。

SQL#1

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, FullName VARCHAR(30));
INSERT INTO @tbl (FullName) VALUES
('John A Smith'),
('John B. Smith'),
('John Smith C'),
('John Smith Z.'),
('Nguyen H. NG');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = SPACE(1);
SELECT t.* 
, c.query('data(/root/r[string-length() gt 2])').value('text()[1]', 'VARCHAR(30)') AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
REPLACE(FullName, @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML)) AS t1(c);

SQL#2

首先，删除一个点。之后只删除一个字母单词。

SELECT t.* 
, c.query('data(/root/r[string-length() gt 1])').value('text()[1]', 'VARCHAR(30)') AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
REPLACE(REPLACE(FullName,'.',@separator), @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML)) AS t1(c);

输出

John SmithJohn Smith约翰·史密斯

ID	全名	结果
1	John A Smith
2	John B.Smith
3	John Smith C	John Smith
4	约翰·史密斯Z
5	Nguyen H.NG	Nguyen NG