SQLServer2019-模糊查询

模糊查询使用like关键字和通配符结合来实现,通配符具体含义如下:

通配符	含义
%	代表匹配0个字符,1个字符或多个字符
_	代表匹配有且只有一个字符
[]	代表匹配范围内
[^]	代表匹配不在范围内

嘿嘿,来点小例题
1.查询出姓刘的员工信息

select*from People where PeopleName like '刘%'

2.查询出名字中含有"晨"的员工信息

select*from People where PeopleName like'%晨%'

3.查询出名字中含有"晨"或者"铭"的员工信息

select*from People where PeopleName like'%晨%'or PeopleName like'%铭%'

4.查询出姓刘的员工信息,名字是两个字

select*from People where PeopleName like'刘_'
select*from People where SUBSTRING(PeopleName,1,1)='刘' and
len(PeopleName)=2

SUBSTRING(‘beautiful’,3,1)
–结果为a,(从第三个取,取一个),意思是在查询beautiful这个词儿时,第三个字符是a,只取一个,那就是a咯

select SUBSTRING('beautiful',3,1)

5.查询名字最后一个字为"文",名字一共三个字的员工

select*from People where SUBSTRING(PeopleName,3,1)='文' and
len(PeopleName)=3
select*from People where PeopleName like'__文'

6.查询出电话号码开头为156的员工信息

select*from People where PeoplePhone like'156________'
select*from People where PeoplePhone like'156%'
select*from People where SUBSTRING(PeoplePhone,1,3)='156'

7.查询出电话号码开头是156,第六个好像是7或者8,最后一个好像是1

select*from People where PeoplePhone like'156__[7,8]____1'

8.查询出电话号码开头是156,第六位是一个2到9之间的一个数字,最后一个数字不是3,4,6,7

select*from People where PeoplePhone like'156__[2,3,4,5,6,7,8,9]____[^3,4,6,7]'
select*from People where PeoplePhone like'156__[2-9]____[^3,4,6,7]'