The STRING_SPLIT is 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.
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 ) |