tsql - How do I parse a full name to pull out first name from these possible values-CodePudding

--How do I pull out first name using tsql from the possible values in a FullName field:

Here are possible values:

Richardson M.D., Asha R
Goines PHD, Jennifer
Goines P.H.D., Kevin G
Bourne M.D., T. David

From the below query, I am able to pull last name fine, but I am not able to get first name correctly. Is it possible with the type of data values?

select 
SUBSTRING(pe2.full_name, 1, CHARINDEX(' ', pe2.full_name) - 1) AS "Rendering Provider:  LastName",
SUBSTRING(pe2.full_name, CHARINDEX(', ', pe2.full_name)   1, len(pe2.full_name)) AS "Rendering Provider:  FirstName",
parsename(replace(pe2.full_name, ' ', ','), 3) as FirstName,
from personnel pe2

CodePudding user response：

For the data posted in the question, and making no assumptions about other formats of data:

DROP TABLE #n;
CREATE TABLE #n (full_name nvarchar(100));
INSERT INTO #n (full_name)
VALUES ('Richardson M.D., Asha R'),
('Goines PHD, Jennifer'),
('Goines P.H.D., Kevin G'),
('Bourne M.D., T. David');

SELECT SUBSTRING(full_name,PATINDEX('%, %',full_name) 2,LEN(full_name)), full_name
FROM #n

CodePudding user response：

parsename doesn't work that way, it returns values from a specified position in a "Dot" delimited string. Example: I have a cat

select parsename(replace('I have a cat', ' ', '.'), 4) as FirstWord
select parsename(replace('I have a cat', ' ', '.'), 3) as SecondWord
select parsename(replace('I have a cat', ' ', '.'), 2) as ThirdWord
select parsename(replace('I have a cat', ' ', '.'), 1) as FourthWord

Try this instead to understand how it is parsing the firstname

parsename(replace(LTRIM(SUBSTRING(pe2.full_name, CHARINDEX(', ', pe2.full_name)   1, len(pe2.full_name))), ' ', '.'), 2) AS FirstName 
parsename(replace(LTRIM(SUBSTRING(pe2.full_name, CHARINDEX(', ', pe2.full_name)   1, len(pe2.full_name))), ' ', '.'), 1) AS MiddleName 
from personnel pe2