Search

Saturday, October 29, 2011

Split Name Into First, Middle and Last Name

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  

2 comments:

  1. This gave me perfect result!
    Thanks, you saved my time :)

    ReplyDelete
  2. Can you help me in parsing name like: Steve Twelve & Mary Twelve and Mr. & Mrs. G. Frank Sixteen
    or Dr. David & Mrs. Linda Thirty or Fortyfive Blue Horsehoe Solutions, Inc.



    ReplyDelete