Split string into rows and columns in SQL Server

This is a simple split function I have created to split a string into a table by delimiters available inside the string. The difference between this split function and other split functions are, it can split the string into columns and as well as rows. You can also use the same function to split your comma separated or other delimiter string into table.


String: Author ~ Unknown | IF ~ YOU ~ PRAY ~ FOR ~ THE ~ RAIN ~ DEAL ~ WITH ~ THE ~ MUD

In above string, my row delimiter would be “|” and column delimiter would be “~”


Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

4 thoughts on “Split string into rows and columns in SQL Server

  • December 23, 2014 at 10:02 pm

    If you knew input string then better to use union all with unpivot I have used that in many scripts in i-engineering better refer that. 🙂

  • March 9, 2016 at 1:27 am

    Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, function to splie string using different delimiters in SQL Server.


Leave a Reply

%d bloggers like this: