Splitting a String in T-SQL

^{Photo by Caspar Camille Rubin on Unsplash}

The STRING_SPLITis now available in SQL Server 2016 and onwards that splits a string using a separator.  For versions of SQL Server before 2016 it can either be done by using a UDF or using PARSENAME .  PARSENAME  is a SQL Server function used for working with linked servers and replication and parsing a name at a certain level, i.e. Servername.Databasename.Ownername.Objectname.

It will work up to 4 parts/levels in the string to parse.

Declare @ObjectName nVarChar(1000) 
Set @ObjectName = 'ContosoDB1.Northwind.dbo.Authors' 

PARSENAME(@ObjectName, 4) as Server --ContosoDB1 
PARSENAME(@ObjectName, 3) as DB --Northwind 
PARSENAME(@ObjectName, 2) as Owner --dbo 
PARSENAME(@ObjectName, 1) as Object --Authors

On the other hand, a UDF such as the one below will accomplish the same task.

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