Let’s assume that you are working with SQL Server database and you need to build an ad-hoc report and split the string data into separate columns. Your data contains specific delimiter and you want to parse the data into respective columns. For example, your data column FullName contains “LastName, FirstName”, you would like to parse your data using comma+space delimiter to separate LastName and FirstName value into respective columns. If you search online, you will find couple of solutions. Online searches may yield result where you need to create a new user defined function or other tricks. Sometimes you don’t have a luxury to create those custom objects for your need in your 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). (Workaround: Replace your delimiter character(s) with “.” (dot/full stop))
- You can split string into maximum of 4 columns.
- All rows must have string 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.
Example
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
Leave a Reply