Split string into table, separator can be more than 1 char

This function has been developed by me and my friend Mihai Ciurescu.

Let's suppose that you have a web page with a listbox (multiple selection) of States and you want to get all customers having properties within your selected States. So, your page will send to the SQL Server a list of all selected State_IDs.

Now, your list of IDs is like '123,43,5465,6788,1231' and you want to split and pivot them into a table like:

Using this function now you can use its table-result to do an INNER JOIN with your_Address table (where you store the State_Id) ON .fn_SplitStringToTable.ItemData = your_Address.State_Id

This is what this

SELECT * FROM dbo.fn_SplitStringToTable('123,43,5465,6788,1231', ',')

returns

RowIndex FromPos ToPos ItemData
1 1 4 123
2 5 7 43
3 8 12 5465
4 13 17 6788
5 18 22 1231

Enjoy!

Luigi and Michael