SQL 模糊查询

使用模糊查询需要在select语句中使用like
在sql中通配符共有４个，分别为＂％＂，＂＿＂，＂［］＂与＂［＾］＂
＂％＂表示可以包含零个或多个字符的任意字符串．
＂＿＂代表任意单个字符．
＂［］＂代表指定范围或集合中的任意一个字符．
＂［＾］＂代表不属于指定范围伙计和中的任意一个字符．

select * from 班级表 where 班级编号　like '%99%'  //返回班级编号字段任意位置包含的字符串＂９９＂的结果集．
select * from 班级表 where 班级编号　like '_2001'  //返回班机编号字段以２００１结尾的字母结果集．
select * from 班级表 where 班级编号　like '［a-b］'  //返回以字母a到字母b开头的所有字符．
select * from 班级表 where 班级编号　like '［^a-b］'   //返回不以字母a到字母b开头的所有字符．

--示例
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PersonInfo_GetSearch]
(
@Family_Code nvarchar(30),
@Name nvarchar(30),
@Sex int, ----性别0是男,1是女,2全部
@Community_ID int,
@HousingEstate_ID int
@StartAge int=0,
@@EndAge int=200
)
AS
select
PersonInfo.*,
FamilyInfo.*,
CommunityInfo.*,
HousingEstateInfo.*
from
PersonInfo
left join
FamilyInfo
on
FamilyInfo.Family_ID=PersonInfo.Family_ID
left join
CommunityInfo
on
FamilyInfo.Community_ID=CommunityInfo.Community_ID
left join
HousingEstateInfo
on
FamilyInfo.HousingEstate_ID=HousingEstateInfo.HousingEstate_ID
where
(@Family_Code is null or @Family_Code='' or(Family_Code like '%' + @Family_Code + '%'))
and
((Name like '%' + @Name + '%')or(@Name is null or @Name=''))
and
((@Sex=2)or(Sex=@Sex))
and
((@Community_ID=0)or(CommunityInfo.Community_ID=@Community_ID))
and
((@HousingEstate_ID=0)or(HousingEstateInfo.HousingEstate_ID=@HousingEstate_ID))
and
PersonInfo.Age between @StartAge AND @EndAge