关于用patindex筛选数据的问题~~~~在线等：）））
表为TAB1，字段BH

BH
1H2101
1H2102
1H2103
1HA101
1HB101
1HC101
...
查询时，输入1H后，只想显示
1H2101
1H2102
1H2103

select * from TAB1 where patindex('''+trim(ed_bh.Text)+'''+''[^a-z]%'',bh)>0
当在‘ed_bh.Text’编辑框输入1h时，数据显示正确，可输入1H2101时，查询不出数据，怎么改呀？求助~~~谢谢：）））


[解决办法]
create table #t
(bh varchar(10))
insert into #t
select '1H2101' union all
select '1H2102' union all
select '1H2103' union all
select '1HA101' union all
select '1HB101' union all
select '1HC101'

select * from #t
where patindex('%1H%',bh)>0 and substring(bh,3,1)not like '[a-z]%'

bh
----------
1H2101
1H2102
1H2103

（所影响的行数为 3 行）
[解决办法]

SQL code

create table tb(BH varchar(10)) insert into tb values('1H2101')  insert into tb values('1H2102')  insert into tb values('1H2103')  insert into tb values('1HA101')  insert into tb values('1HB101')  insert into tb values('1HC101') godeclare @bh as varchar(10)set @bh = '1H'select * from tb where left(bh,len(@bh)) = @bh and substring(bh , len(@bh)+1 , 1) not between 'A' and 'Z' /*BH         ---------- 1H21011H21021H2103（所影响的行数为 3 行）*/set @bh = '1H2101'select * from tb where left(bh,len(@bh)) = @bh and substring(bh , len(@bh)+1 , 1) not between 'A' and 'Z'/*BH         ---------- 1H2101（所影响的行数为 1 行）*/drop table tb