Changing substrings position in a string

Transact-SQL
1

I have a string like this (there is a carriage return at the end of each line):

[1]
[A] Tom
[B] Cherry
[C] Mickey
[D] Donald
[End]

Using T-SQL, I want to change it to:

[1] Tom
[A] Cherry
[B] Mickey
[C] Donald
[D]
[End]

Please advise. Thanks.

2 
Declare @CR char = char(10)
Declare @string varchar(100) = '[1]' + @CR 
+ '[A] Tom' + @CR 
+ '[B] Cherry' + @CR 
+ '[C] Mickey' + @CR 
+ '[D] Donald' + @CR 
+ '[End]' + @CR;

Print @string;

With PARSESTRING As
(
	Select
		ROW_NUMBER() Over (Order By Len(Tag), Tag) As Ordinal,
		X2.Tag,
		X2.Value
	From string_split(@string,@cr) SS
	Cross Apply (Select NullIf(Patindex('%]%',SS.value), 0) As TagEnd) X
	Cross Apply (Select NullIf(Left(SS.value, TagEnd), '') As Tag, NullIf(Right(SS.value, Len(SS.value) - TagEnd) ,'') As Value) X2
	Where X2.Tag Is Not Null
) 
Select
	@String = STRING_AGG(PARSESTRING.Tag + IsNull(MOVEVALUES.Value, ''), @CR)
From PARSESTRING 
Left Join
(
	Select
		ROW_NUMBER() Over (Order By Ordinal) As Ordinal,
		Value
	From PARSESTRING
	Where Value Is Not Null
) MOVEVALUES On PARSESTRING.Ordinal = MOVEVALUES.Ordinal

Print @string
3

Which specific version of SQL? LEAD/LAG is available from SQL 2012 on, which makes this easy (assuming you do have some identity or datetime or some other column that can give the rows a specific order).

4

Thank you for your response, but I am running SQL Server 2014 which does not recognize STRING_SPLIT function. This is the latest version I can run on Windows 7. Can you rewrite the code that works with 2014? Thanks

5

You can replace string_split with a similar function. I'd recommend the 2012 enhanced version of Jeff Moden's DelimitedSplit8K as described here

You'll also need to replace string_agg with an alternate string aggregation technique, such as the XML stuff method, which should look something like:

Declare @CR char = char(10)
Declare @string varchar(100) = '[1]' + @CR 
+ '[A] Tom' + @CR 
+ '[B] Cherry' + @CR 
+ '[C] Mickey' + @CR 
+ '[D] Donald' + @CR 
+ '[End]' + @CR;

Print @string;

With PARSESTRING As
(
	Select
		ROW_NUMBER() Over (Order By Len(Tag), Tag) As Ordinal,
		X2.Tag,
		X2.Value
	From [DelimitedSplit8k](@string,@cr) SS
	Cross Apply (Select NullIf(Patindex('%]%',SS.Item), 0) As TagEnd) X
	Cross Apply (Select NullIf(Left(SS.Item, TagEnd), '') As Tag, NullIf(Right(SS.Item, Len(SS.Item) - TagEnd) ,'') As Value) X2
	Where X2.Tag Is Not Null
) 
Select
	@String = Stuff((
		Select
			@CR + PARSESTRING.Tag + IsNull(MOVEVALUES.Value, '')

		From PARSESTRING 
		Left Join
		(
			Select
				ROW_NUMBER() Over (Order By Ordinal) As Ordinal,
				Value
			From PARSESTRING
			Where Value Is Not Null
		) MOVEVALUES On PARSESTRING.Ordinal = MOVEVALUES.Ordinal
		For XML Path('')
	), 1, 1, '')

Print @string

I don't think there is anything else 2014+ in there but i don't have an install of it to be sure.