In some cases, familiarity with the use of SQL Server wildcard can help us solve many problems.
-- Use the _ operator to search for the names of the three letters ending with an in the person table
Useadventureworks2012;
Go
Select firstname, lastname
From person. Person
Where firstname like '_'
Order by firstname;
--- Use the [^] operator to search for all persons whose names start with Al and whose third letter is not a in the contact table
Useadventureworks2012;
Go
Select firstname, lastname
From person. Person
Where firstname like 'al [^ A] %'
Order by firstname;
--- Use the [] operator to find the ID and name of all adventure works employees whose addresses contain four postal codes
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]';
Result set:
Employeeid firstname lastname postalcode
--------------------------------------
290 Lynn tsoflias
3000
-- Name a table in Chinese and English (refer to the Code in the Forum)
Create Table Tb (namenvarchar (20 ))
Insert into tbvalues ('kevin ')
Insert into tbvalues ('kevin Liu ')
Insert into tbvalues ('Liu ')
Select *, 'eng' from tbwherepatindex ('% [A-Z] %', name)> 0and (patindex ('% [A-Sit] %', name) = 0)
Union all
Select *, 'cn' from tbwherepatindex ('% [sitting] %', name)> 0 andpatindex ('% [A-Z] %', name) = 0
Union all
Select *, 'eng & cn' from tbwhere (patindex ('% [sitting] %', name)> 0) andpatindex ('% [A-Z] % ', name)> 0
Result set:
Name
--------------------------
Kevin Eng
Liu CN
Kevin Liu ENG & CN
(3 row (s) affected)