Many a times there are situations when you want to split a string in a field based on a seperator like a comma(",") or maybe a semicolon(";"). Here's an SQL function which would allow you to do just that.

CREATE FUNCTION [dbo].[Split_with] (@sep varchar(max), @s varchar(max))

RETURNS @mytab table (rowid int,keys varchar(max))

AS

BEGIN

WITH Pieces(pn, start, stop) AS (

SELECT 1, 1, CAST(CHARINDEX(@sep, @s) AS INT)

UNION ALL

SELECT pn + 1, stop + 1, CAST(CHARINDEX(@sep, @s, stop + 1) AS INT)

FROM Pieces

WHERE stop > 0

)

INSERT @mytab

SELECT pn,

SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s

FROM Pieces

OPTION (MAXRECURSION 10000)

RETURN

END

To make the function work you need to call it like:-

SELECT * FROM Split_With(',',(SELECT {field} FROM {table} Term where {filter a single row} ))

Ofcourse the function will work on a single field at a time. All you need to do a recurse through the entire table using a cursor or a while loop.

Hope this helps whoever needs it.