Parsing First Middle Last name

Question

Post reply

Parsing First Middle Last name

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
November 28, 2012 at 1:12 pm

#285601

Would anyone know what would be wrong with this query?
I'm using it to change names in this format: Doe, John M. to three fields: John M Doe
thus stripping out the comma after the first name (if present) and the period after the middle initial if exists and is present:
SELECT LTRIM(SubString(CustomerName,1,ISNULL(Nullif(CHARINDEX(' ',CustomerName),0),1000))) As First_Name,
LTRIM(SUBSTRING(CustomerName,CharIndex(' ',CustomerName),
CASE WHEN (CHARINDEX(' ',CustomerName,CHARINDEX(' ',CustomerName)+1)-CHARINDEX(' ',CustomerName))<=0 then 0
else CHARINDEX(' ',CustomerName,CHARINDEX(' ',CustomerName)+1)-CHARINDEX(' ',CustomerName) end )) as Middle_Name,
LTRIM(SUBSTRING(CustomerName,ISNULL(Nullif(CHARINDEX(' ',CustomerName,Charindex(' ',CustomerName)+1),0),CHARINDEX(' ',CustomerName)),
CASE WHEN Charindex(' ',CustomerName)=0 then 0 else LEN(CustomerName) end)) as Last_Name From dbo.Data$
Go

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

what if your data is not in that exact format?

/*

--Results

First_Name Middle_Name Last_Name

Doe, John M.

*/

With [data$] (CustomerName)

AS

(

SELECT 'Doe, John M.'

)

SELECT LTRIM(SubString(CustomerName,1,ISNULL(Nullif(CHARINDEX(' ',CustomerName),0),1000))) As First_Name,

LTRIM(SUBSTRING(CustomerName,CharIndex(' ',CustomerName),

CASE WHEN (CHARINDEX(' ',CustomerName,CHARINDEX(' ',CustomerName)+1)-CHARINDEX(' ',CustomerName))<=0 then 0

else CHARINDEX(' ',CustomerName,CHARINDEX(' ',CustomerName)+1)-CHARINDEX(' ',CustomerName) end )) as Middle_Name,

LTRIM(SUBSTRING(CustomerName,ISNULL(Nullif(CHARINDEX(' ',CustomerName,Charindex(' ',CustomerName)+1),0),CHARINDEX(' ',CustomerName)),

CASE WHEN Charindex(' ',CustomerName)=0 then 0 else LEN(CustomerName) end)) as Last_Name From Data$

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

I've only found 5 out of 8,000 records which I simply hand scrubbed... all else were in stated format

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 3

I can go with changing the first part to "SELECT LTRIM(SubString(CustomerName,-1" and that does the trick in this case