Split a field in an SQL table
By Ashwin on Wednesday 4 July 2007, 07:34 - MsSQL - Permalink
Many a times there are situations when you want to split a string in a field based on a separator like a comma(",") or maybe
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.