# 关于用patindex筛选数据的有关问题~）））

www.myexceptions.net  网友分享于：2013-01-12  浏览：4次

BH
1H2101
1H2102
1H2103
1HA101
1HB101
1HC101
...

1H2101
1H2102
1H2103

select * from TAB1 where patindex('''+trim(ed_bh.Text)+'''+''[^a-z]%'',bh)>0

------解决方案--------------------
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')
go
declare @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
----------
1H2101
1H2102
1H2103

（所影响的行数为 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```