如何验证RegExp语法正确性？CASE匹配逻辑异常排查

我用以下正则表达式编写了CASE逻辑，但匹配结果的数量不符合预期——仅在包含4种状态的首个应用场景中匹配正确。不确定代码是否存在问题，该如何验证？

CASE
WHEN REGEXP_MATCH(Lead Status, '.*1st appointment - agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment - Email – Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment - F2F – Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment - Provisional – Call back.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment - Provisional – Cancelled.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment - Telephone – Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment Cancelled - Pre Appt – Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment Cancelled At Office – Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment - No Show - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment - Not Held - Disabled.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment Held - 2nd Appt Booked - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment Held - DNQ - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment Held - ERQ Completed - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment Held - Not Proceeding - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appointment Held - On Review - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*1st Appt Held - Over 6 Months.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*2nd Appointment - Disabled.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*2nd Appointment Cancelled - Pre Appt - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*2nd Appointment Held - Not Proceeding - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*2nd Appointment Held - On Review - Adviser.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Busy - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Call back - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*DNQ - Age - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*DNQ - Investments - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*DNQ - LTV - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*DNQ – Other – Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*DNQ - Property Type - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Does Not Qualify - Disabled.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Duplicate – Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Future Contact – Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not Interested - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not made enquiry - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Uncontactable.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Wanted A Face to Face - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Wanted Information - Agent.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*RIO Wanted.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Remortgage Wanted.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*RS Pension Referral.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not Interested - Alt funding.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not interested - Cant raise funds.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not Interested – Competitor.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not Interested - Didnt want a call.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not interested - Down sizing.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not Interested - Not made inquiry.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not Interested - Wanted a quote.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not Interested - Wanted rates.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Not Interested – Hang Up.*')
THEN 1
WHEN REGEXP_MATCH(Lead Status, '.*Existing Customer Query.*')
THEN 1
else 0
end

• 提取未匹配样本：执行查询时，同时返回Lead Status和CASE生成的匹配标记（比如命名为matched_flag），筛选matched_flag = 0的记录，查看这些未被匹配的状态值，检查是否存在拼写错误、特殊字符差异。
• 单独测试正则：针对有疑问的状态值，单独运行SELECT REGEXP_MATCH('具体状态值', '对应的正则表达式')，验证单个正则是否能正确匹配目标值。
• 检查大小写敏感性：不同SQL引擎的正则匹配默认规则不同，部分引擎区分大小写。比如你第一个正则用了小写的appointment - agent，后续用了大写的Appointment - Agent，若数据中的状态大小写与正则不一致，会导致匹配失败。可以尝试在正则开头添加(?i)修饰符开启不区分大小写匹配（比如(?i).*1st appointment - agent.*）。
• 排查特殊字符：注意代码中存在两种破折号——半角-和全角–（例如DNQ – Other – Agent中的破折号），如果数据中的破折号类型与正则不一致，会无法匹配。可以统一替换为同类型破折号，或者用[-–]来匹配两种类型的破折号。
• 统计分支匹配数：对每个WHEN条件单独执行计数查询，比如SELECT COUNT(*) FROM 表名 WHERE REGEXP_MATCH(Lead Status, '.*1st Appointment - Email – Agent.*')，对比每个分支的实际匹配数与预期值，定位哪个分支未按预期匹配。

原代码的正则表达式过于冗余，可将相似规则合并，降低维护成本，同时解决大小写和特殊字符问题：

CASE
WHEN REGEXP_MATCH(Lead Status, '(?i).*1st appointment(.*agent|.*email.*agent|.*f2f.*agent|.*provisional.*call back|.*provisional.*cancelled|.*telephone.*agent|.*cancelled.*pre appt.*adviser|.*cancelled at office.*agent|.*no show.*adviser|.*not held.*disabled|.*held.*2nd appt booked.*adviser|.*held.*dnq.*adviser|.*held.*erq completed.*adviser|.*held.*not proceeding.*adviser|.*held.*on review.*adviser|.*appt held.*over 6 months).*')
OR REGEXP_MATCH(Lead Status, '(?i).*2nd appointment(.*disabled|.*cancelled.*pre appt.*adviser|.*held.*not proceeding.*adviser|.*held.*on review.*adviser).*')
OR REGEXP_MATCH(Lead Status, '(?i).*(busy[-–]agent|call back[-–]agent|dnq[-–]age[-–]agent|dnq[-–]investments[-–]agent|dnq[-–]ltv[-–]agent|dnq[-–]other[-–]agent|dnq[-–]property type[-–]agent|does not qualify[-–]disabled|duplicate[-–]agent|future contact[-–]agent|not interested[-–]agent|not made enquiry[-–]agent|uncontactable|wanted a face to face[-–]agent|wanted information[-–]agent|rio wanted|remortgage wanted|rs pension referral|not interested[-–]alt funding|not interested[-–]cant raise funds|not interested[-–]competitor|not interested[-–]didnt want a call|not interested[-–]down sizing|not interested[-–]not made inquiry|not interested[-–]wanted a quote|not interested[-–]wanted rates|not interested[-–]hang up|existing customer query).*')
THEN 1
ELSE 0
END

这里用(?i)统一开启不区分大小写，用[-–]匹配两种破折号，将同类状态合并到单个正则中，减少重复代码。

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