If you have SQL Server 2016 then there is a new
STRING_SPLIT[
^] function, but if you have an earlier version there is a full discussion of various methods at
Split strings the right way - or the next best way[
^]
[EDIT] - This is the version I use at home, see the code for the author credit:
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @source varchar(max) = 'http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/'
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
Which you would call like this:
select * from dbo.fnSplitString('Testing_TestEnvironment=open=2017-03-23','=')
Or if you want the results formatted as per your expected output then call it like this:
SELECT [1] AS [machinename], [2] AS [status], [3] AS [date]
FROM (
select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS X, * from dbo.fnSplitString('Testing_TestEnvironment=open=2017-03-23','=')
) as qry
PIVOT
(
MAX(splitdata)
FOR [X] IN ([1],[2],[3])
)AS pvt