How do you remove the middle initials from the full name? By creating a custom function?

I want to convert FullName to FullNameWithoutInitials:

How do I remove any words equal to 1 (with or without a dot) character such as (A or A.) from a string?

Thank you!

CodePudding user response：

Please try the following solution.

Obviously, it cannot take care of all possible scearios with names.

It using XML and XQuery to tokenize FullName column.

The XPath predicate [string-length() gt 2] is removing any words less than or equal to 2 chars.

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

First, removing a dot. After that removing just a single letter words.

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);

Output