If you need to create an ad-hoc report and split the data into separate columns, you will find it difficult to search online. Online searches may yield result where you need to create a new user defined function or other tricks those are either time consuming or not possible in production environment.
You are really looking for a built in function that can easily satisfy your need without too much complexity.
Well, SQL Server has a built-in function that you can use to split string data into multiple columns. But before you get excited, there are some limitations.
Limitations of This Trick
If you are fine with the following limitations, you may use this built-in function for your need.
- It only splits string with “.” (dot/full stop) so if needed you need to replace your delimiter character(s) with “.” (dot/full stop)
- It only splits maximum of 4 parts
- All rows must have data in the exact parts. You cannot have one row with 4 parts and another row with 2 parts
- Result (not input) must be of nvarchar(128) or varchar(256). If you have longer result, it will return null value.
SQL Built-in Function – PARSENAME
PARSENAME is a built-in SQL function that parses object string name into Server Name, Database Name, Schema Name and Object Name. Object name contains “.” (dot/full stop) as a separator.
This built-in function uses “.” (dot) as a separator but does not verify if the object exists in the database or not. This makes it ideal to pass any data in this function and get result.
The first thing you need to do is replace the separator available in your text with “.” (dot). If your separator is “, ” (comma followed by space), ” – ” (space followed by dash followed by space), use REPLACE function to replace it with “.” (dot) character.
Now use the result as a PARSENAME parameter to get the value as a column.
Let’s say you have an full name to parse. All your rows contain full name in “Last Name, First Name” format. In this format, the separator is “, ” comma followed by space.
You want to create 2 columns – FirstName and LastName.
So I am replacing “, ” (comma+space) character with “.” (dot)
PARSENAME function 2nd argument is in reverse order – i.e. from right to left. So make sure you are using the correct argument based on your need.
DECLARE @EmployeeName VARCHAR(120) SELECT @EmployeeName = 'Monpara, Vishal' SELECT PARSENAME(REPLACE(@EmployeeName, ', ', '.'), 1) AS FirstName, PARSENAME(REPLACE(@EmployeeName, ', ', '.'), 2) AS LastName