如何在特定单词后截断字符串?

Question

我有一串数据类型nvarchar(max)，如下所示：

declare @cbCheckdate nvarchar(max) ='
{"request_id":"364202","final_decision":"FAIL","derived_Attribute_1":"PASS|Number of active MFI :1",
"derived_Attribute_4":"PASS|Total Exposure + Applied Amount :53051.0",
"derived_Attribute_3":"PASS|Number of Total Active Institutions :2",
"derived_Attribute_2":"FAIL|Overdue Amount:17984.0","derived_Attribute_5":"PASS|Write off amount:0.0",
"cbResponseMsg":"Final Decision:FAIL || Number of active MFI :1 || Total Exposure + Applied Amount :53051.0 
|| Number of Total Active Institutions :2 || FAILOve'

我需要截断上面的字符串，如下所示：

declare @cbCheckdate nvarchar(max) ='{"request_id":"364202","final_decision":"FAIL","derived_Attribute_1":"PASS|Number of active MFI :1",
"derived_Attribute_4":"PASS|Total Exposure + Applied Amount :53051.0",
"derived_Attribute_3":"PASS|Number of Total Active Institutions :2",
"derived_Attribute_2":"FAIL|Overdue Amount:17984.0","derived_Attribute_5":"PASS|Write off amount:0.0"'

基本上我需要做的是：如果我的字符串包含这个单词cbResponseMsg那么我需要删除这个单词和它后面的所有文本。

Answer 1

> 由于您使用的是SQL Server2014并且还没有内置的JSON支持，因此我可能会为此编写一个小函数：

CREATE OR ALTER FUNCTION dbo.TruncateAfter(@Input NVARCHAR(MAX), @Delimiter NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(MAX);
DECLARE @DelimiterPos INT;
SET  @DelimiterPos = CHARINDEX(@Delimiter, @Input);
IF (@DelimiterPos > 0)
SET @Result = TRIM(LEFT(@Input, @DelimiterPos - 1));
ELSE
SET @Result = @Input;
RETURN @Result;
END

现在，您可以使用两个参数调用此函数 - 您的输入和您要查找的"分隔符"。如果找到分隔符，则其位置上的任何文本都将被截断 - 如果分隔符未出现在输入中，则返回整个输入：

DECLARE @cbCheckdate NVARCHAR(MAX) = N'
{"request_id":"364202","final_decision":"FAIL","derived_Attribute_1":"PASS|Number of active MFI :1",
"derived_Attribute_4":"PASS|Total Exposure + Applied Amount :53051.0",
"derived_Attribute_3":"PASS|Number of Total Active Institutions :2",
"derived_Attribute_2":"FAIL|Overdue Amount:17984.0","derived_Attribute_5":"PASS|Write off amount:0.0",
"cbResponseMsg":"Final Decision:FAIL || Number of active MFI :1 || Total Exposure + Applied Amount :53051.0 
|| Number of Total Active Institutions :2 || FAILOve'
DECLARE @Delimiter NVARCHAR(MAX) = N'cbResponseMsg';
SELECT 
dbo.TruncateAfter (@cbCheckdate, @Delimiter)

应返回所需的输出。

Answer 2

此输入可能是 JSON 格式，因此如果您使用 SQL Server 2016+，您可以使用JSON_MODIFY()函数从输入 JSON 中删除cbResponseMsg密钥：

陈述：

DECLARE @cbCheckdate nvarchar(max) = '{
"request_id":"364202",
"final_decision":"FAIL",
"derived_Attribute_1":"PASS|Number of active MFI :1",
"derived_Attribute_4":"PASS|Total Exposure + Applied Amount :53051.0",
"derived_Attribute_3":"PASS|Number of Total Active Institutions :2",
"derived_Attribute_2":"FAIL|Overdue Amount:17984.0",
"derived_Attribute_5":"PASS|Write off amount:0.0",
"cbResponseMsg":"Final Decision:FAIL || Number of active MFI :1 || Total Exposure + Applied Amount :53051.0 || Number of Total Active Institutions :2 || FAILOve"
}'
SELECT @cbCheckdate = JSON_MODIFY(@cbCheckdate, '$.cbResponseMsg', NULL)
SELECT @cbCheckdate

结果：

{
"request_id":"364202",
"final_decision":"FAIL",
"derived_Attribute_1":"PASS|Number of active MFI :1",
"derived_Attribute_4":"PASS|Total Exposure + Applied Amount :53051.0",
"derived_Attribute_3":"PASS|Number of Total Active Institutions :2",
"derived_Attribute_2":"FAIL|Overdue Amount:17984.0",
"derived_Attribute_5":"PASS|Write off amount:0.0"
}

Answer 3

你可以这样做

set @cbCheckdate = left(@cbCheckdate, CHARINDEX('cbResponseMsg', @cbCheckdate) -2)  select @cbCheckdate

Answer 4

这是一种安全的方法，无需用户定义的函数，使用内置函数stuff、charindex、reverse、len和sign：

declare @cbCheckdate nvarchar(max) ='
{"request_id":"364202","final_decision":"FAIL","derived_Attribute_1":"PASS|Number of active MFI :1",
"derived_Attribute_4":"PASS|Total Exposure + Applied Amount :53051.0",
"derived_Attribute_3":"PASS|Number of Total Active Institutions :2",
"derived_Attribute_2":"FAIL|Overdue Amount:17984.0","derived_Attribute_5":"PASS|Write off amount:0.0",
"cbResponseMsg":"Final Decision:FAIL || Number of active MFI :1 || Total Exposure + Applied Amount :53051.0 
|| Number of Total Active Institutions :2 || FAILOve';
declare @delimiter nvarchar(100) = '"cbResponseMsg"';
SELECT  REVERSE(
STUFF(
REVERSE(@cbCheckdate), 
1,
CHARINDEX(REVERSE(@delimiter), REVERSE(@cbCheckdate)) + 
LEN(@delimiter) * SIGN(CHARINDEX(@delimiter, @cbCheckdate)),
'')
)

以下是从内到外的细分：

• 如果未找到分隔符，CHARINDEX(@delimiter, @cbCheckdate)将返回 0，如果在字符串中找到分隔符，则将返回正整数。
• SIGN将返回0的0，1正数或负数(与此情况无关(-1。
• CHARINDEX(REVERSE(@delimiter), REVERSE(@cbCheckdate)) + LEN(@delimiter) * SIGN(CHARINDEX(@delimiter, @cbCheckdate))将返回0找不到分隔符，或者返回一个正整数，表示分隔符开头到字符串末尾之间的字符数。
• STUFF函数将一个空字符串放入源字符串中，从第一个字符(1(开始，并替换它作为第三个参数的字符数。
• 最后，由于这一切都是在反转的字符串上完成的，因此再次reverse结果以正确的顺序返回字符串。

Answer 5

你可以试试这个

SELECT
CASE
WHEN @cbCheckdate LIKE '%cbResponseMsg%' THEN LEFT(@cbCheckdate, CHARINDEX('cbResponseMsg', @cbCheckdate)-3)
ELSE @cbCheckdate
END

Answer 6

尝试将其作为简单有效的查询：

SELECT
LEFT(@cbCheckdate, CHARINDEX('cbResponseMsg', @cbCheckdate)-3)
WHERE 
@cbCheckdate LIKE '%cbResponseMsg%'
UNION ALL
SELECT
@cbCheckdate
WHERE 
@cbCheckdate NOT LIKE '%cbResponseMsg%'