.NET/C#/SQL Server 2014 查找末尾仅差整数的相同NVarchar自定义ID生成递增ID

基础环境与业务约束 #

• 技术栈：.NET Framework 4.6.1、C#、SQL Server 2014
• 存储规则：业务表中CustomId为nvarchar(255)类型，不允许修改表结构拆分独立整数列存储末尾序列值
• 核心需求：查找所有「除末尾连续整数部分外，前缀与指定基础串完全一致」的CustomId记录，排除中间夹杂其他字符的误匹配，提取匹配记录末尾整数的最大值，生成下一个递增的自定义ID

前期尝试的SQL缺陷 #

先后两次编写的查询逻辑均无法满足匹配要求：

1. 初次编写的查询语句

   SELECT CustomId
   FROM dbo.CustomUnit
   WHERE CustomId LIKE 'parentunit_childunit%'
   AND SUBSTRING(CustomId,LEN(CustomId),1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
   

   问题：只要CustomId以基础串开头、末尾是数字就会被命中，无法排除类似parentunit_childunit_someotherthing2这类中间存在其他差异字符的误匹配。
2. 调整后的查询语句

   SELECT CustomId
   FROM dbo.CustomUnit
   WHERE CustomId LIKE 'parentunit_childunit_'
   AND SUBSTRING(CustomId,LEN(CustomId),1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
   

   问题：用单下划线占位数字位，仅能匹配末尾为1位整数的记录，会遗漏末尾为多位整数（如10、33）的场景。

通用前置规则 #

• 无法提前预知末尾整数的位数、同基础串对应的匹配记录总量
• 字符串中间位置的数字不影响匹配逻辑判断
• 如果C#端实现逻辑更简便，可优先采用C#端方案

匹配效果参照 #

测试库存储的CustomId全量数据如下：

CustomId
-------------------------------------
parentunit_childunit
parentunit_childunit1
parentunit_childunit2
parentunit_childunit_someotherthing10
parentunit_childunit_someotherthing11
parentunit_childunit_someotherthing12
parentunit_childunit_somethingelse3
parentunit_childunit_somethingelse4
parentunit_childunit_somethingelse5
parentunit_childunit10
parentunit_childunit33
parentunit_testchild
parentunit_testchild1
parentunit_newchild
parentunit1_newchild1
parentunit1_newchild2
anotherparent_anotherchild
anotherparent_anotherchild1
newparent_childunit5
newparent_childunit6
newparent_childunit7

当需要为parentunit_childunit系列生成新ID时，正确的匹配结果为：

Result
-------------------------------------
parentunit_childunit
parentunit_childunit1
parentunit_childunit2
parentunit_childunit10
parentunit_childunit33

最终提取到的最大末尾整数为33，生成的新自定义ID为parentunit_childunit34。

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