我们可以从简化到目前为止所拥有的内容开始，我还将添加足够的内容以使其成为一个完整的查询，这样我们就可以在后面的步骤中看到它：

SELECT 
   right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)) [DerivedPrimaryKey]
FROM I

完成这些工作后，我们现在可以使用它作为删除字段尾部的源代码：

SELECT 
   reverse(substring(reverse(step1)
      , charindex('_', reverse(step1))+1
      , len(step1)
  )) [DerivedPrimaryKey]
FROM (
    SELECT right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)) [step1]
    FROM I
) T

注意嵌套层，当然，你可以删除嵌套，但这意味着每次看到step1时都要复制整个内部表达式（幸好我花了时间简化它）：

SELECT 
   reverse(substring(reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
      , charindex('_', reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld))))+1
      , len(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
  ))
FROM I

现在回到刚才的表达式：

reverse(substring(reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
      , charindex('_', reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld))))+1
      , len(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
  ))

在这里看到它的工作原理：
https://dbfiddle.uk/nFO4Vwhm
还有一个替代表达式可以节省一次函数调用：

left(   right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)), 
   coalesce(
      nullif(
        charindex('_', 
            right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)) 
         ) -1, -1,
      ), 
      len( right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)) )
    )
)

还有两个选项，一个是使用parsename（），前提是数据段不超过4个，另一个是使用JSON数组

示例

Declare @YourTable Table ([MyFld] varchar(50))  Insert Into @YourTable Values 
 ('P_36840')
,('U_216137')
,('C_203134_H')
,('C_203134_W')
 
Select *
      ,UsingParseName = reverse(parsename(reverse(replace(MyFld,'_','.')),2))
      ,UsingJSONValue = json_value('["'+replace(MyFld,'_','","')+'"]','$[1]')
 From  @You

结果

MyFld       UsingParseName  UsingJSONValue
P_36840     36840           36840
U_216137    216137          216137
C_203134_H  203134          203134
C_203134_W  203134          203134

我们可以这样做：

Declare @testData Table ([MyFld] varchar(50));
 Insert Into @testData (MyFld)
 Values ('P_36840')
      , ('U_216137')
      , ('C_203134_H')
      , ('C_203134_W');
 
 Select *
      , second_element = substring(v.MyFld, p1.pos, p2.pos - p1.pos - 1)
   From @testData                                               As td
  Cross Apply (Values (concat(td.MyFld, '__')))                 As v(MyFld)  -- Make sure we have at least 2 delimiters
  Cross Apply (Values (charindex('_', v.MyFld, 1) + 1))         As p1(pos)   -- First Position
  Cross Apply (Values (charindex('_', v.MyFld, p1.pos) + 1))    As p2(pos)   -- Second Position

如果第一个元素中的字符数是固定的，则可以简化为：

Select *
      , second_element = substring(v.MyFld, 3, charindex('_', v.MyFld, 4) - 3)
   From @testData td
  Cross Apply (Values (concat(td.MyFld, '_'))) As v(MyFld)

如果期望的字符不总是存在，并且我不需要结果值，我通常会尝试伪造SQL：

SELECT SUBSTRING(field_Calculated, 1, CHARINDEX('_', field_Calculated) - 1)  
FROM (SELECT SUBSTRING(MyFld, CHARINDEX('_', MyFld) + 1, LEN(MyFld)) + '_' As field_Calculated 
  FROM MyTable) T

我认为这很清楚，但我真的很喜欢@JohnCappalletti建议的ParseName解决方案。
如果只有一个数值，可以使用string_split：

SELECT * FROM MyTable
CROSS APPLY string_split(MyFld, '_')
WHERE ISNUMERIC(value) = 1

无论哪种方式，在决定最佳方法之前，您都必须仔细查看数据。

您的数据

Declare @Table Table ([MyFld] varchar(100))  
Insert Into @Table
([MyFld] ) Values 
('P_36840')
,('U_216137')
,('C_203134_H')
,('C_203134_W')

使用SubString、Left和PatIndex

select 
  Left(
    SubString(
      [MyFld], 
      PatIndex('%[0-9.-]%', [MyFld]), 
      8000
    ), 
    PatIndex(
      '%[^0-9.-]%', 
      SubString(
        [MyFld], 
        PatIndex('%[0-9.-]%', [MyFld]), 
        8000
      ) + 'X'
    )-1
  )  as DerivedPrimaryKey
from 
  @Table