Examples of using wildcards in SQL Server

Time:2021-4-1

In some cases, being familiar with the use of SQL Server wildcards can help us solve many problems simply.

--Use_ Operator to find a three letter name ending with an in the person table
USEAdventureWorks2012;
GO
SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName LIKE'_an'
ORDER BY FirstName;
 
---Use the [^] operator to find all people 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 address has a four digit zip code
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
--Distinguish the Chinese and English names in a table (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-Z]%, name) = 0)
union all
Select *,'cn'from tbwherepatindex ('% [A-Z]%', name) > 0andpatindex ('% [A-Z]%', name) = 0
union all 
Select *,'eng & CN 'from tbwhere (patindex ('% [A-Z]%, name) > 0) and patindex ('% [A-Z]%, name) > 0)

Result set:

name        
-------------------- ------
kevin        Eng
Liu cn
Kevin Liu eng & cn
 
(3 row(s) affected)