SQL错误信息字符串匹配映射标准化错误码方案咨询

处理SQL表内错误数据时，需要将携带动态内容的同类错误信息聚合映射为统一标准错误码：例如错误信息The date year on policy found does not match the request映射为POLICY_DATE_MISMATCH，No results found for PolicyId...这类后接动态保单号的错误统一映射为同类错误码，过滤错误信息中附带的保单号、具体日期、运营商名称等动态冗余内容，实现准确映射。

错误码查找表 #

Create Table Lookup_ErrorCode
(
    ErrorMessage Varchar(100),
    ErrorCode Varchar(100)
)
        
Insert into Lookup_ErrorCode values('Invalid Login for Carrier', 'INVALID_LOGIN')
Insert into Lookup_ErrorCode values('Error getting data for Policy', 'ERROR_DATA_POLICY')
Insert into Lookup_ErrorCode values('The date year on policy found does not match the request', 'POLICY_DATE_MISMATCH')
Insert into Lookup_ErrorCode values('Error on Policy Effective Date. Policy', 'ERROR_POLICY_EFFECTIVE_DATE')
Insert into Lookup_ErrorCode values('No results found for  PolicyId', 'NO_RESULTS_FOR_POLICY')
Insert into Lookup_ErrorCode values('Cannot find loan info though payor is set to mortgagee', 'LOAN_NOT_FOUND')
Insert into Lookup_ErrorCode values('No matching dates were found.', 'MATCHING_DATES_NOT_FOUND')

匹配自定义函数 #

Create function CodeErrorMessage(@ErrorMessage varchar(max))
Returns VARCHAR(100) 
AS
BEGIN
    --Declare @ErrorMessage VARCHAR(max) = 'Invalid Login for Carrier: Universal Property & Casualty';

    SELECT ErrorCode from Lookup_ErrorCode where [ErrorMessage] like LEFT(@ErrorMessage, 15)+'%'
    Return 0;
END 

当前实现仅截取错误信息前15个字符做前缀匹配，规则僵化，容易出现错配，无法兼容动态内容出现在错误信息不同位置的场景，匹配准确率不足。

• 优化查找表结构与匹配逻辑，替换固定长度前缀匹配
  将原有查找表的固定错误文本字段替换为通配符匹配模式，新增优先级字段，优先匹配更长、更精准的模式，避免短规则误判，改造后代码如下：

  -- 重建带优先级的查找表
  DROP TABLE IF EXISTS Lookup_ErrorCode;
  Create Table Lookup_ErrorCode
  (
      MatchPattern Varchar(200),
      ErrorCode Varchar(100),
      MatchPriority INT -- 数值越大匹配优先级越高
  );
  
  -- 插入适配动态内容的通配符规则
  Insert into Lookup_ErrorCode values('Invalid Login for Carrier%', 'INVALID_LOGIN', 10);
  Insert into Lookup_ErrorCode values('Error getting data for Policy%', 'ERROR_DATA_POLICY', 10);
  Insert into Lookup_ErrorCode values('The date year on policy found does not match the request%', 'POLICY_DATE_MISMATCH', 20);
  Insert into Lookup_ErrorCode values('Error on Policy Effective Date%', 'ERROR_POLICY_EFFECTIVE_DATE', 20);
  Insert into Lookup_ErrorCode values('No results found for%PolicyId%', 'NO_RESULTS_FOR_POLICY', 20);
  Insert into Lookup_ErrorCode values('Cannot find loan info though payor is set to mortgagee%', 'LOAN_NOT_FOUND', 20);
  Insert into Lookup_ErrorCode values('No matching dates were found%', 'MATCHING_DATES_NOT_FOUND', 10);
  

  同步修改自定义函数，按优先级、模式长度倒序匹配，命中即返回，未命中时返回兜底错误码：

  Create or Alter function CodeErrorMessage(@ErrorMessage varchar(max))
  Returns VARCHAR(100) 
  AS
  BEGIN
      DECLARE @ResCode VARCHAR(100);
      SELECT TOP 1 @ResCode = ErrorCode 
      FROM Lookup_ErrorCode 
      WHERE @ErrorMessage LIKE MatchPattern
      ORDER BY MatchPriority DESC, LEN(MatchPattern) DESC;
      RETURN ISNULL(@ResCode, 'UNKNOWN_ERROR');
  END 
  

  该方案改造成本极低，对于动态内容仅出现在错误信息后缀、固定核心文本完整的场景，匹配准确率可达95%以上。
• 新增错误信息预处理步骤
  如果存在动态内容插入在错误信息中间的场景（例如Policy POL2024001 effective date is invalid这类编号插在文本中间的错误），可以在匹配前先做文本清洗：使用正则替换（SQL Server可通过CLR集成正则函数，或用内置REPLACE/TRANSLATE函数处理）将连续数字、UUID、日期格式字符串、括号/冒号后跟随的动态属性内容统一替换为空，清洗后再和标准规则做匹配，进一步提升准确率。
• 增加未知错误兜底迭代机制
  新建错误匹配日志表，将所有命中UNKNOWN_ERROR兜底码的原始错误信息自动落表，定期梳理后补充到Lookup_ErrorCode规则表中，持续迭代覆盖全量错误场景，长期运行后匹配准确率可趋近100%。

内容的提问来源于stack exchange，提问作者CrazyCoder