1. SQL Tutorial (5) Wildcards, IN, BETWEEN

Wildcards

SQL Server

LIKE operators used w/ ‘%’ and ‘_’ wildcards:

Using the % Wildcard:

SELECT * FROM Customers
WHERE City LIKE 'ber%'; #a City starting w/ "ber"

SELECT * FROM Customers
WHERE City LIKE '%es%'; #a City containing the pattern "es"

Using the _ Wildcard

SELECT * FROM Customers
WHERE City LIKE '_ondon'; #a City starting w/ any character, followed by "ondon"

SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

Using the [charlist] Wildcard
Example 1: selects all customers w/ a City starting w/ “b”, “s”, or “p”

SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

Example 2: selects all customers w/ a City starting w/ “a”, “b”, or “c”

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

Using the [!charlist] Wildcard
Examples: select all customers w/ a City NOT starting w/ “b”, “s”, or “p”

SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';

---

IN Operator

• allows you to specify multiple values in a WHERE clause
• a shorthand for multiple OR conditions

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
#WHERE Country IN (SELECT Country FROM Suppliers);

---

BETWEEN Operator

• selects values within a given range
  • values can be numbers, text, dates
• inclusive: begin & end values are included

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

NOT BETWEEN:

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 and 20;

BETWEEN with IN:

SELECT * FROM Products
WHERE Price Between 10 and 20
AND CategoryID NOT IN (1,2,3);

BETWEEN Text values:

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

NOT BETWEEN Text values:

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

BETWEEN Dates:

SELECT * FROM Orders
WHERE OrderDate BETWEEN #08/01/1996# AND #07/31/1996#;

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31'

---

This post is based on W3School's articles about SQL.