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
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
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) A socialist is someone who will give you the shirt off *someone else's* back.
March 24, 2022 at 1:30 am
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.
September 29, 2022 at 2:05 pm
I'm following up on this. The query has been working great for many months now, it has only found a few outliers that have truly bad name formats in the 'full name' field. But the icing on the cake is that after FOUR YEARS of requesting, the vendor supplying the data file just added dedicated first name, last name, and middle name fields in the data file. Better late than never.
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply