Splitting out Full Name into Last, First, Middle Initial

Question

Post reply

Splitting out Full Name into Last, First, Middle Initial

usererror

Ten Centuries

Points: 1143
More actions
March 23, 2022 at 8:08 pm
Go to Answer
#4004970
Hello DBAs,

I am driving the struggle bus here. I've been browsing multiple older threads on splitting out full names here and on Stack Overflow, and it's true that no data source is exactly alike so there is no one size fits all solution for this type of question. I have monthly data from a large insurance carrier that will not budge on adding Last, First, Middle Initial fields instead of using "Member Name" with it all crammed in one field. I have found through various the code that gets me close, but when there isn't a middle initial (which will be common) the query below puts the First name in the Middle Name column.

Copy/Paste the code below to your SSMS. Anyone willing to assist a newb, I greatly appreciate it. I have cannibalized the query below. Ideally, I'm looking to make this take the parsed out Last, First, and Middle Initial and insert it into their own columns, not just select it.
```
CREATE TABLE [dbo].[TestNames2](
[MBR_NM] [varchar](255) NULL,
[MBR_LastName] [varchar](150) NULL,
[MBR_FirstName] [varchar](50) NULL,
[MBR_MiddleName] [varchar](1) NULL
)

INSERT INTO [dbo].[TestNames2]
           ([MBR_NM]
           ,[MBR_LastName]
   ,[MBR_FirstName]
   ,[MBR_MiddleName]
           )
     VALUES
           ('Smith, John H',null,null,null),
   ('Wayne, John',null,null,null),
   ('Kirk, James T',null,null,null),
   ('Picard, Jean L',null,null,null),
   ('Riker, William',null,null,null)

select * from TestNames2

SELECT MBR_NM,
CASE WHEN parsename(replace(MBR_NM, ', ', '.'), 4) IS NOT NULL THEN 
    parsename(replace(MBR_NM, ', ', '.'), 4) ELSE
CASE WHEN parsename(replace(MBR_NM, ', ', '.'), 3) IS NOT NULL THEN 
parsename(replace(MBR_NM, ', ', '.'), 3) ELSE
parsename(replace(MBR_NM, ', ', '.'), 2) end END as MBR_Lastname
,CASE WHEN parsename(replace(MBR_NM, ' ', '.'), 3) IS NOT NULL THEN 
   parsename(replace(MBR_NM, ' ', '.'), 2) ELSE NULL END as MBR_FirstName
,parsename(replace(MBR_NM, ' ', '.'), 1) as MBR_MiddleName
from  TestNames2
```
- This topic was modified 2 years, 6 months ago by usererror. Reason: missed a crucial character in the code
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.

ScottPletcher

SSC Guru

Points: 100262

March 23, 2022 at 8:47 pm

Answer

#4004975

INSERT INTO [dbo].[TestNames2] ( [MBR_NM] ) VALUES
    ('von Richthofen, Manfred')

SELECT
    MBR_NM,
    RTRIM(LEFT(MBR_NM, comma_location - 1)) AS MBR_LastName,
    LTRIM(RTRIM(SUBSTRING(MBR_NM, comma_location + 1, LEN(MBR_NM) - 
        comma_location - name_has_middle_init * 2))) AS MBR_FirstName,
    CASE WHEN name_has_middle_init = 0 THEN '' ELSE RIGHT(MBR_NM, 1) END AS MBR_MiddleName
FROM dbo.TestNames2
CROSS APPLY (
    SELECT CHARINDEX(',', MBR_NM) AS comma_location,
        CASE WHEN RIGHT(MBR_NM, 2) LIKE ' [A-Z]' THEN CAST(1 AS tinyint) ELSE CAST(0 AS tinyint) END AS name_has_middle_init
) AS ca1

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

usererror Ten Centuries Points: 1143 More actions · Answer 1

@scottpletcher,

Your query worked perfectly and simply adding "Insert Into MyTableName" above it did exactly what I needed it to do. Thank you.

Now, I can build my SSIS package around the monthly flat file we receive and include this bit of code into one of the SQL Execute steps.

-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.