create table dbo.table1 (
    Field1 varchar(30),
    Field2 varchar(30));
go

insert into dbo.table1
values
    ('11','BX-00006767'),
    ('12','BX-00006767'),
    ('13','BX-00006767'),
    ('14','BX-00006767'),
    ('DT-1205-006049','BX-00006768');
go
select Field1, Field2 from dbo.table1;
go
select cast(right(Field1,len(field1)-1) as int)
from dbo.table1
where Field2 = 'BX-00006767';
go
drop table dbo.table1;
go