As we all know, values such as a person's name or address are stored in either a concatenated string or as individual columns for each part of the whole value in SQL Server data tables and views. The data could be stored in a single column with all the data, in two columns separating the person's name from their address, or in multiple columns with a column for each piece of the total value of the data. With this, DBA's are inevitably always having to concatenate or parse the values to suit our customer's needs.
Don't worry about that. SQL Server has a built-in function that allows you to split string data into multiple columns. Let's check it out!
How to Split String into Multiple Columns
PARSENAME is a built-in SQL function and it is used to parse object string names into Server Name, Database Name, Schema Name, and Object Name. Object name contains "." (dot/full stop) as a separator.
PARSENAME uses "." (dot) as a separator but does not verify if the object exists in the database or not. In this case, it is ideal to pass any data in this function and get results.
To begin with, you should replace the separator available in your text with "." (dot). If you use separator ", " (a comma followed by space) or " - " (space followed by a dash followed by space), please use REPLACE function to replace it with "." (dot) character.
Now, let us use the result as a PARSENAME parameter to get the value as a column.
Let's say you have a full name to parse. All your rows contain the full name in "Last Name, First Name" format. In this format, the separator is ", " comma followed by space.
And then, you want to create 2 columns - FirstName and LastName.
So, let us replace ", " (comma+space) character with "." (dot)
PARSENAME function 2nd argument is in reverse order - i.e. from right to left. So please make sure you are using the correct argument based on your requirements.
DECLARE @EmployeeName VARCHAR(120) SELECT @EmployeeName = 'Monpara, Vishal' SELECT PARSENAME(REPLACE(@EmployeeName, ', ', '.'), 1) AS FirstName, PARSENAME(REPLACE(@EmployeeName, ', ', '.'), 2) AS LastName