更改字符串中的子字符串位置 [英] Changing substring position in a string
问题描述
我有一个这样的字符串(每行末尾有一个回车符):
$
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]
使用SQL函数,我想将其更改为:
Using SQL functions, I want to change it to:
[1] Tom
[A] Cherry
[B] Mickey
[C] Donald
[D]
[End]
请告知。谢谢。
Please advise. Thanks.
推荐答案
DECLARE @st VARCHAR(100)='[1]
[A] Tom
[B] Cherry
[C] Mickey
[D] Donald
[结束]'
$
; WITH cte
AS
(
SELECT 值,LEFT(值,CHARINDEX(' ]',value))AS c1,
SUBSTRING(value,4,LEN(value))c2
FROM string_split( REPLACE(REPLACE(@ st,CHAR(13)+ CHAR(10),'
DECLARE @st VARCHAR(100)='[1]
[A] Tom
[B] Cherry
[C] Mickey
[D] Donald
[End]'
;WITH cte
AS
(
SELECT value ,LEFT(value, CHARINDEX(']',value)) AS c1,
SUBSTRING(value,4,LEN(value)) c2
FROM string_split(REPLACE(REPLACE(@st,CHAR (13)+CHAR(10),'
'),'',''),'
'),' ','') ,'
')
),cte1
AS
(SELECT c1,LEAD(c2)以上的情况(ORDER BY(SELECT 0) )
IS NULL或LEAD(c2)OVER(ORDER BY(SELECT 0))LIKE'%]'THEN''ELSE
LEAD( c2)OVER(ORDER BY(SELECT 0)) END c3 FROM cte
)SELECT c1 + c3 FROM cte1
')
),cte1
AS
( SELECT c1, CASE WHEN LEAD(c2) OVER (ORDER BY (SELECT 0))
IS NULL OR LEAD(c2) OVER (ORDER BY (SELECT 0)) LIKE '%]' THEN '' ELSE
LEAD(c2) OVER (ORDER BY (SELECT 0)) END c3 FROM cte
) SELECT c1+c3 FROM cte1
这篇关于更改字符串中的子字符串位置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!