Remove middle initials from full name such as (A or H.)

Montreal 0
Viewed 54

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

I want to convert FullName to FullNameWithoutInitials:

FullName FullNameWithoutInitials
John A Smith John Smith
John B. Smith John Smith
John Smith C John Smith
John Smith Z. John Smith

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!

sql-server sql
1 Answers

Please try the following solution.

Obviously, it cannot take care of all possible scenarios 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

ID FullName Result
1 John A Smith John Smith
2 John B. Smith John Smith
3 John Smith C John Smith
4 John Smith Z. John Smith
5 Nguyen H. NG Nguyen NG
Yitzhak Khabinsky0
Related