如何在T-SQL中的特定单词后截断字符串？
我有一个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那么我需要删除此单词以及其后的所有文本。
展开
收起
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
阿里云 AI 助理回答
在SQL Server中，你可以使用CHARINDEX()函数来找到特定字符串的位置，然后结合LEFT()函数来截取你想要的部分。以下是如何实现你的需求的SQL语句：
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 || FAIL'}
DECLARE @keyword NVARCHAR(100) = 'cbResponseMsg'
DECLARE @keywordPos INT = CHARINDEX(@keyword, @cbCheckdate)
IF @keywordPos > 0
BEGIN
SET @cbCheckdate = LEFT(@cbCheckdate, @keywordPos - 1)
END
SELECT @cbCheckdate
这段代码首先定义了原始字符串@cbCheckdate和要查找的关键字@keyword（即cbResponseMsg）。然后，它使用CHARINDEX()函数找到@keyword在@cbCheckdate中的位置。如果找到了关键字（即@keywordPos > 0），则使用LEFT()函数截取从字符串开头到关键字前一个字符的所有内容，并将结果重新赋值给@cbCheckdate。
最后，通过SELECT语句输出处理后的字符串。这样，你就能得到不包含cbResponseMsg及其后面所有文本的新字符串。
有帮助
无帮助
AI 助理回答生成答案可能存在不准确，仅供参考
相关问答