简化列中的值

你可以用 SUBSTRING :

SELECT CAST(SUBSTRING(col1, 4,3) AS INT) AS col1, col2, col3
FROM tab_name;

UPDATE

UPDATE tab_name
SET col1 = CAST(SUBSTRING(col1, 4,3) AS INT);

不会有前导零: Rextester Demo

当数字总是在正确的位置时,这是相当容易的。有趣的是,下面是如何解决以下示例数据的问题:

col1
--------------
xxx001xxx1
xxxx005xxx2
xxxxxx0010xxx3
xxx00015xxx1
xxx00007xxx2
xxx0033xxx1

-- sample data
declare @sometable table (col1 varchar(100));
insert @sometable
values ('xxx001xxx1'), ('xxxx005xxx2'), ('xxxxxx0010xxx3'), 
       ('xxx00015xxx1'), ('xxx00007xxx2'), ('xxx0033xxx1');

-- solution
with prep as ( select col1, nbrStart = substring(col1, patindex('%[0-9][0-9]%',col1), 100)
               from @sometable)
select col1,
       nbr = cast(substring(nbrStart, 1, patindex('%[^0-9]%', nbrStart)-1) as int)
from prep;

后果

col1                               nbr
---------------------------------- -----------
xxx001xxx1                         1
xxxx005xxx2                        5
xxxxxx0010xxx3                     10
xxx00015xxx1                       15
xxx00007xxx2                       7
xxx0033xxx1                        33