SQL Server中通配符的使用示例

在某些情况下熟悉sql server 通配符的使用可以帮助我们简单的解决很多问题。

--使用_运算符查找person表中以an结尾的三字母名字
useadventureworks2012;
go
select firstname, lastname
from person.person
where firstname like'_an'
order by firstname;
 
---使用[^]运算符在contact表中查找所有名字以al开头且第三个字母不是字母a的人
useadventureworks2012;
go
select firstname, lastname
from person.person
where firstname like'al[^a]%'
order by firstname;
 
---使用[]运算符查找其地址中有四位邮政编码的所有adventure works雇员的id和姓名
useadventureworks2012;
go
select e.businessentityid, p.firstname, p.lastname, a.postalcode
fromhumanresources.employeeas e
inner join person.personas pon e.businessentityid= p.businessentityid
inner join person.businessentityaddressas eaon e.businessentityid=ea.businessentityid
inner join person.addressas aon a.addressid= ea.addressid
where a.postalcodelike'[0-9][0-9][0-9][0-9]';

 
结果集：

 employeeid   firstname   lastname   postalcode
----------   ---------   ---------   ----------
290       lynn      tsoflias   3000

--将一张表中名字为中英文的区分出来（借鉴论坛中的代码）
create table tb(namenvarchar(20))
 
insert into tbvalues('kevin')
insert into tbvalues('kevin刘')
insert into tbvalues('刘')
 
select *,'eng'from tbwherepatindex('%[a-z]%',name)>0and(patindex('%[吖-坐]%',name)=0)
union all
select *,'cn'from tbwherepatindex('%[吖-坐]%',name)>0andpatindex('%[a-z]%',name)=0
union all 
select *,'eng&cn'from tbwhere(patindex('%[吖-坐]%',name)>0)andpatindex('%[a-z]%',name)>0

结果集：

name        
-------------------- ------
kevin        eng
刘          cn
kevin刘       eng&cn
 
(3 row(s) affected)