Click here to Skip to main content
14,735,693 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a string in SQL column in below structure

Want to get data in below formal

Start Date | End Date | Value 
Date1      | Date2    | Value1
Date3      | Date4    | Value2
Date5      | Date6    | Value3

Please help

What I have tried:

Solution Added for the problem below
Updated 9-Dec-19 19:35pm
Maciej Los 9-Dec-19 8:31am
What have you tried (including code)?
As far as i see, you need split string based on set of delimiters: {'\', '-', '='}

DECLARE @FullString AS NVARCHAR(1000) = 'Date1-Date2=Value1\Date3-Date4=Value2\Date5-Date6=Value3'
	   ;with Data as 
	   (SELECT Split.a.value('.', 'VARCHAR(100)') AS Row  
	   FROM  (SELECT   
       CAST ('<M>' + REPLACE(@FullString, '\', '</M><M>') + '</M>' AS XML) AS String) AS A CROSS APPLY String.nodes ('/M') AS Split(a))
	  select Row, 
	  SUBSTRING(Row,CHARINDEX('-',Row)+1,CHARINDEX('=',Row)-CHARINDEX('-',Row)-1) as EndDate,
	  SUBSTRING(Row,0,CHARINDEX('-',Row)) as StartDate ,
	  SUBSTRING(Row ,CHARINDEX('=', Row)+1, LEN(Row) - CHARINDEX('=', Row)) Value
	  from Data
I'd suggest to start here: STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]

For older version of MS SQL Server (than 2016) i'd do that this way:
DECLARE @t2s AS NVARCHAR(1000) = 'Date1-Date2=Value1\Date3-Date4=Value2\Date5-Date6=Value3'

	--initial query
	SELECT 1 AS RowNo, LEFT(@t2s, CHARINDEX('\',@t2s)-1) AS RowValue, RIGHT(@t2s, LEN(@t2s) - CHARINDEX('\',@t2s)) AS Remainder
	WHERE CHARINDEX('\',@t2s)>0
	--recursive part
	SELECT RowNo +1 AS RowNo, LEFT(Remainder, CHARINDEX('\',Remainder)-1) AS RowValue, RIGHT(@t2s, LEN(Remainder) - CHARINDEX('\',Remainder)) AS Remainder
	FROM MyRows 
	WHERE CHARINDEX('\',Remainder) >0
	SELECT RowNo+1 AS RowNo, Remainder AS RowValue, NULL AS Remainder
	FROM MyRows 
	WHERE CHARINDEX('\',Remainder) =0
	LEFT(RowValue, CHARINDEX('-', RowValue)-1) AS Col1,
	SUBSTRING(RowValue, CHARINDEX('-', RowValue)+1, LEN(RowValue) - CHARINDEX('=', RowValue)-1) AS Col2,
	RIGHT(RowValue, LEN(RowValue)-CHARINDEX('=', RowValue)) AS Col3
WHERE CHARINDEX('-', RowValue)>0 AND CHARINDEX('=', RowValue)>0

RowNo	Col1	Col2	Col3
1	Date1	Date2	Value1
2	Date3	Date4	Value2
3	Date5	Date6	Value3
Komal Dattani 9-Dec-19 8:39am
Not on SQL 2016
Maciej Los 9-Dec-19 8:45am
So, you should provide more information about SQL Server version. What is yours?
CHill60 9-Dec-19 9:30am
STRING_SPLIT was added in SQL Server 2016
Komal Dattani 10-Dec-19 0:44am
Thanks @Maciej Los this works as well
Maciej Los 10-Dec-19 2:01am
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900