Example of using wildcard characters in SQL Server _mssql

Being familiar with the use of SQL Server wildcard characters in some situations can help us solve a lot of problems simply.

--Use the _ operator to find the three-letter name at the end of the person table
USEAdventureWorks2012;
Go
SELECT FirstName, LastName from
person.person
WHERE FirstName like ' _an ' order by
FirstName;
 
---Use the [^] operator to find all names in the Contact table that start with Al and the third letter is not the letter a
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 USEAdventureWorks2012 of all Adventure Works employees whose addresses have four-bit postal codes
;
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

--Differentiate a table name from Chinese and English (refer to the code in the Forum)
CREATE table TB (Namenvarchar)
 
insert INTO Tbvalues (' Kevin ')
insert Into Tbvalues (' Kevin Liu ')
insert into tbvalues (' Liu ')
 
select *, ' Eng ' from Tbwherepatindex ('%[a-z]% ', name) > 0and (Patindex ('%[-sit]% ', name) =0)
UNION ALL
SELECT *, ' CN ' from Tbwherepatindex ('%[acridine-sit]% ', name) > 0andpatindex ('%[a-z]% ', name) =0
UNION ALL 
SELECT *, ' ENG&CN ' from Tbwhere (Patindex ('%[acridine-sit]% ', name) > 0) Andpatindex ('%[a-z]% ', name) >0

Result set:

Name        
--------------------------
Kevin        Eng
Liu          CN
Kevin Liu       eng&cn
 
(3 Row (s) affected)