A good Microsoft SQL String Split Function

Sooner or later any programmer that deals with SQL database will notice that there is no easy to use Text SPLIT function in Microsoft SQL, and by that I mean a function that gives you both the split text and the position of each and lets you select a specific position's data.

Here is one such function that makes life much easier for me:


The usage of it is pretty simple:

SELECT * FROM fn_SplitText('This|is|a|test','|');

The Split function actually returns a temporary table containing the split data, in this case, it will return the following:

 ID  |  Data
-----------------
  1  | This
  2  | is
  3  | a
  4  | test

So if I wanted to select the 2nd substring out of that split I would just say:

SELECT Data FROM fn_SplitText('This|is|a|test','|') WHERE ID = 2;

Which would return "is" to me.

Pete Soheil
DigiOz Multimedia
www.digioz.com