CODEWITHSUNDEEP
sql-servert-sql
Yaba_Middle
Yaba_Middle

Reputation: 57

Eliminating Middle Initials from a name string

I am trying to eliminate middle initials whenever I come across them in a table:

For example if I have John D Doe, I want my query to return John Doe. Here is my query.

declare @Name varchar(100);

select @Name = 'James D Doe';

select left(@Name, len(@Name)-charindex(' ', @Name,0))+' '+right(@Name, charindex(' ', @Name,0));

Right now my query is returning the full string. How do I strip off the Middle initials whenever I come across them? My desired output is James Doe.

Upvotes: 2

Views: 911

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

This should work for your specific use case where you only have a single middle name (or initial):

SELECT CASE WHEN LEN(REPLACE(@Name, ' ', '')) = LEN(@Name) - 2
THEN
    CONCAT(SUBSTRING(@Name, 1, CHARINDEX(' ', @Name, 0)),
           SUBSTRING(@Name,
                     CHARINDEX(' ', @Name, CHARINDEX(' ', @Name, 0) + 1) + 1,
                     LEN(@Name) - CHARINDEX(' ', @Name, CHARINDEX(' ', @Name, 0) + 1)),
ELSE @Name
END

Upvotes: 1

Related Questions