如果您的数据模式/格式是固定的，则以下内容将有所帮助

SELECT RIGHT(PARSENAME(REPLACE('123-010-456','-','.'),2),2)

您可以将以下内容合并为一个查询，但为了更好地理解，我将其分解为3个步骤。

DECLARE @String nvarchar(20) = '123-010-456'
DECLARE @MiddlePart nvarchar(20)
DECLARE @FirstNonZero int

--Get the middle part
SET @MiddlePart = SUBSTRING(@String,
                 CHARINDEX('-',@String)+1, --Find the 1st dash
                 LEN(@String)-CHARINDEX('-',@String)-CHARINDEX('-',Reverse(@String)) --Find length between two dashes
                 ) 

--Get the First Non zero number
SET @FirstNonZero = (SELECT MIN(x) 
                     FROM (SELECT CHARINDEX('1',@MiddlePart) as x WHERE CHARINDEX('1',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('2',@MiddlePart) as x WHERE CHARINDEX('2',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('3',@MiddlePart) as x WHERE CHARINDEX('3',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('4',@MiddlePart) as x WHERE CHARINDEX('4',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('5',@MiddlePart) as x WHERE CHARINDEX('5',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('6',@MiddlePart) as x WHERE CHARINDEX('6',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('7',@MiddlePart) as x WHERE CHARINDEX('7',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('8',@MiddlePart) as x WHERE CHARINDEX('8',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('9',@MiddlePart) as x WHERE CHARINDEX('9',@MiddlePart)>0 
                     ) a)

--Final
Select SUBSTRING(@MiddlePart,@FirstNonZero,LEN(@MiddlePart)-@FirstNonZero+1)

你可以尝试提取中间部分并将其转换为int -这将删除所有前导零，同时保留尾随零...如果需要，你可以将其转换回varchar。下面是一个示例：

DECLARE @t TABLE(testval nvarchar(40))

INSERT INTO @t VALUES
('123-010-456')
,('1234-1-456789')
,('12-00010-4')
,('1-0007-4')

SELECT *
      ,SUBSTRING(testval, CHARINDEX('-', testval)+1, CHARINDEX('-', testval, CHARINDEX('-', testval)+1)-CHARINDEX('-', testval)-1)
      ,CAST(SUBSTRING(testval, CHARINDEX('-', testval)+1, CHARINDEX('-', testval, CHARINDEX('-', testval)+1)-CHARINDEX('-', testval)-1) AS INT)
  FROM @t