时间戳

首页 > 解决方案 > Deleting a string between two carriage returns tsql

string - Deleting a string between two carriage returns tsql

问题描述

Very new to SQL so I appreciate your patience in advance. I have a column in a table that stores a particular set of instructions; each instruction is encapsulated by a carriage return.
eg: char(13)+ @instruction1 + char(13)...
@Instruction1 is a string of variable length but I do know a certain part of the string eg: @instruction1 = some string + @knownstring + some string.
So we have char(13) + (some string + @knownstring + some string) +char(13).
I want to replace this entire line with ''.
Identifying it just using the @knownstring.

Is this possible?

Thanking you all again, I really appreciate your assistance

select replace(replace(column,@knownsting,''),char(13),'') 
from table 
where key=1235

Replaces only the @knownstring but I also need to replace the surrounding text between the two char(13)

标签： stringtsqlreplace

解决方案

You might try something along this:

DECLARE @KnownString VARCHAR(50)='Keep This'
DECLARE @YourString VARCHAR(MAX)='blah' + CHAR(13) + 'dummy keep this dummy more' + CHAR(13) + 'Something without the known part' + CHAR(13) + 'Again with Keep THIS';

SELECT STUFF(
(
    SELECT CHAR(13) + CASE WHEN CHARINDEX(@KnownString,LineText)>0 THEN @KnownString ELSE LineText END
    FROM (SELECT CAST('<x>' + REPLACE(@YourString,CHAR(13),'</x><x>') + '</x>' AS XML)) A(Casted)
    CROSS APPLY Casted.nodes('/x') B(fragment)
    OUTER APPLY (SELECT fragment.value('text()[1]','nvarchar(max)')) C(LineText)
    FOR XML PATH(''),TYPE
).value('.','nvarchar(max)'),1,1,'');

The result

blah
Keep This
Something without the known part
Keep This

The idea

The string is transformed to XML by replacing the line breaks with XML tags. Now we can query all text lines separately, check them for the known string, do the needed manipulation, and finally reconcatenate all fragments using the XML-trick (together with STUFF to get rid of the leading CHAR(13)).

Remarks

Using v2016 I'd use the split-string approach with OPENJSON and starting with v2017 there is STRING_AGG() to make the reconcatenation easier.

推荐阅读