Split function to show delimiter separated value in separate columns

In one scenario I needed to split a column’s value to show separated values into a single column, suppose in one column we have users name where this column can be contained first name, middle name or Last Name or any one of them so we need to show three separate columns for each user containing first name, middle name and last name in separate columns in the result set as:

 

Here in source table data you can see a delimiter is being used so we can use this delimiter to split data for showing in separate columns as:

·         First I have created a table for storing sample data:

Create TABLE #Temp(UserID INT,Name VARCHAR(50))

 

INSERT #Temp SELECT 1,'Vidhyasagar /Kumar /Sharma'

INSERT #Temp SELECT 2,'Sathya /Singh /Dhawan'

INSERT #Temp SELECT 3,'Madhu /Kumar'

INSERT #Temp SELECT 4,'Manoj'

 

Below is the query where we are using CHARINDEX and SUBSTRING function to split values for showing in separate columns:

 

SELECT UserID,

 

      Case when CHARINDEX(' /',Name)=0 then Name else

      SUBSTRING(Name,1,CHARINDEX(' /',Name)-1) end  FirstName

        

       ,Case when Charindex(' /',SUBSTRING(Name,CHARINDEX(' /',Name)+2,LEN(Name)))=0 then

       Case when CHARINDEX(' /',Name)=0 then Null else

        SUBSTRING(Name,CHARINDEX(' /',Name)+2,LEN(Name)) end else

      Left(SUBSTRING(Name,CHARINDEX(' /',Name)+2,LEN(Name)),Charindex(' /',SUBSTRING(Name,CHARINDEX(' /',Name)+2,LEN(Name))))end MiddleName

           

       ,Case when Charindex(' /',SUBSTRING(Name,CHARINDEX(' /',Name)+2,LEN(Name)))=0 then  Null else

       Substring(SUBSTRING(Name,CHARINDEX(' /',Name)+2,LEN(Name)) ,Charindex(' /',SUBSTRING(Name,CHARINDEX(' /',Name)+2,LEN(Name)))+2,Len(SUBSTRING(Name,CHARINDEX(' /',Name)+2,LEN(Name)))) end LastName

        

FROM #Temp

 

Execute above query and you will get result as:

 

If you want to create a user defined function to split a delimiter separated string value than go through this post: Split delimiter separated value in SQL Server