Use below query to split Name into First, Middle and Last name.
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 CustomerMaster
Go
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 CustomerMaster
Go
This gave me perfect result!
ReplyDeleteThanks, you saved my time :)
Can you help me in parsing name like: Steve Twelve & Mary Twelve and Mr. & Mrs. G. Frank Sixteen
ReplyDeleteor Dr. David & Mrs. Linda Thirty or Fortyfive Blue Horsehoe Solutions, Inc.
Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time sql server dba online training
ReplyDelete