Click here to Skip to main content
14,691,183 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
Date1-Date2=Value1\Date3-Date4=Value2\Date5-Date6=Value3

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
Posted
Updated 9-Dec-19 19:35pm
v3
Comments
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: {'\', '-', '='}

I'd suggest to start here: STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]

[EDIT]
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'

;WITH MyRows AS
(
	--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
	UNION ALL
	--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
	UNION ALL
	SELECT RowNo+1 AS RowNo, Remainder AS RowValue, NULL AS Remainder
	FROM MyRows 
	WHERE CHARINDEX('\',Remainder) =0
)
SELECT RowNo, 
	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
FROM MyRows
WHERE CHARINDEX('-', RowValue)>0 AND CHARINDEX('=', RowValue)>0

Result:
RowNo	Col1	Col2	Col3
1	Date1	Date2	Value1
2	Date3	Date4	Value2
3	Date5	Date6	Value3
   
v3
Comments
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.
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
   

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