Split a string into records based on delimiter, SQL Server 2005

If you have a string which is separated with some delimiter, say comma. And if you think you need to split the string based on the delimiter and return as a recordset, then this function is for you.

This function will split a string into records based on the delimiter

Usage:

SELECT * FROM dbo.SplitStr('Bangalore,Kerala,Mumbai',',')

The result will be

Bangalore
Kerala
Mumbai

This may be helpful if you want to use this in queries like

Select * from Table1 Where City in (SELLECT * FROM dbo.SplitStr('Bangalore,Kerala,Mumbai',','))

Which will return you all the records where the City is either Bangalore, Kerala or Mumbai like in our example...

Function : SplitStr