How to Split the string value into different column value

We have split string and populate its value into rows. For example   “A, B, C” is split and converted into rows with single column value like:

            A

            B

            C

But how can we split the string and put their values in different columns. Suppose we have person name in single column and his name is separated using comma for first name, last name and middle name, we need to split that string from that column and need to populate all these three values in individual column.

It can be done using the REPLACE() and PARSENAME() SQL functions.

Here is the example that demonstrates the use of PARSE function for splitting the string values and put that into the different column values.

DECLARE @name NVARCHAR(100)

SET @name='FName,MName,LName'

Now we replace the comma value with “.”, that is require for parsing the string like array in C#.

Replace the string with “.” Instead “,” using:

SET @name=REPLACE(@name,',','.')

Now finally we parse the person name into different column values by PARSENAME() function.

SELECT

      PARSENAME(@name,3) FName,

      PARSENAME(@name,2) MName,

      PARSENAME(@name,1) LName

Execute the whole query and it gives the result as: