[求助]复杂一点的模糊查询问题,对一个字母+数字组合的字段进行查询,按其数字为条件

[Rail100]

我的数据
MyID MyName
1 ABCD123456
2 ABCD12345
3 ABCD1234
4 ABCD123
5 ABCD12
6 ABCD1
7 ZZZZ123456
8 ZZZZ12345
9 ZZZZ1234
10 ZZZZ123
11 ABCD998


现在我需要一条查询语句，把所有ABCD开头，后面数字小于1000的纪录都查找出来，也就是：
ABCD123
ABCD12
ABCD1
ABCD998


之前有个类似的问题大家可以参考
http://community.csdn.net/Expert/topic/3616/3616841.xml?temp=.5447351

[yingqing]

select * from mytble where substring(myname,1,4)='ABCD' and convert(int,substring(myname5,4))<1000

[Rail100]

如果存在一条ABCDE999的数据,那么执行convert(int,substring(myname,5,4))<1000,这时等于把E999转换成数字,应该会出错吧,这个有办法解决吗,是否通过进一步的筛选?

[yingqing]

union all select * from mytable
drop table mytable

create 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

[yingqing]

select * from mytble where substring(myname,1,4)='ABCD' and substring(myname,5,1)in([0-9]) and convert(int,substring(myname5,4))<1000

[Rail100]

我补充一下,字母后面就是数字,不会字母之后跟数字再跟字母.不过字母的长度不定,数字的长度也不定

[yingqing]

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

[Rail100]

谢谢.受益匪浅