union all select * from mytable drop table mytablecreate table mytable(MyId int, MyName varchar(10)) Insert into mytable select '1','ABCD123456' union all select '2','ABCD12345' union all select '3','ABCD1234' union all select '4','ABCD123' union all select '5','ABCD12' union all select '6','ABCD1' union all select '7','ZZZZ123456' union all select '8','ZZZZ12345' union all select '9','ZZZZ1234' union all select '10','ZZZZ123'select * from mytable where substring(myname,1,4)='ABCD' and convert(int,substring(myname,5,4))<1000 結果: 4 ABCD123 5 ABCD12 6 ABCD1
select * from mytble where substring(myname,1,4)='ABCD' and substring(myname,5,1)in([0-9]) and convert(int,substring(myname5,4))<1000
select * from mytable where substring(myname,1,4)='ABCD' and substring(myname,5,1)like '[0-9]' and convert(int,substring(myname,5,4))<1000
drop table mytablecreate table mytable(MyId int, MyName varchar(10))
Insert into mytable
select '1','ABCD123456'
union all select '2','ABCD12345'
union all select '3','ABCD1234'
union all select '4','ABCD123'
union all select '5','ABCD12'
union all select '6','ABCD1'
union all select '7','ZZZZ123456'
union all select '8','ZZZZ12345'
union all select '9','ZZZZ1234'
union all select '10','ZZZZ123'select * from mytable where substring(myname,1,4)='ABCD' and convert(int,substring(myname,5,4))<1000
結果:
4 ABCD123
5 ABCD12
6 ABCD1