Functionality such as Split() is for the most of us something we take for granted in whatever programming language we are using.
There is however no Split method in MSSQL. So when I was faced with the problem of splitting strings into multiple rows it got a bit tricky.
I ended up with the function below which will take two arguments, the string to work with, and the separator to split on.

CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
   WITH Pieces(n, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
   )
   SELECT n,
   SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
   FROM Pieces
)

 
Here is an example of what this code will do:

SELECT * FROM dbo.CustomSplit('x','aaaaaaxbbbbbbbbbxcccccccc')

SmallImage


Author