|
|
In the following queries we'll use a number of string functions that helps
us find if the CustomerName in table Customers starts from A,B,C
characters. Here i'd like to emphasize that the same objective can be achived
by at least 3 different ways:
-- using functinon INSTR()
SELECT CustomerID, CustomerName
FROM Customers
WHERE INSTR(CustomerName,'A') = 1
OR INSTR(CustomerName,'B') = 1
OR INSTR(CustomerName,'C') = 1
ORDER BY CustomerName;
|
The results will be as following:
| CustomerID | CustomerName |
| 13 | Aida Yespica |
| 6 | Anna Matias |
| 14 | Antonella Musentah |
| 3 | Barbara Spears |
| 17 | Bruce Davenport |
| 22 | Christian Barth |
Using MID() function with equal and
and MID() with
LIKE word can bring us the same result:
SELECT CustomerID, CustomerName
FROM Customers WHERE MID(CustomerName,1,1) = 'A';
ELECT CustomerID, CustomerName
FROM Customers WHERE MID(CustomerName,1,1)
LIKE '%A%'
|
Instead of writing every condition separately, we can group all using
MID() together with IN
+ array of values:
SELECT CustomerID, CustomerName
FROM Customers
WHERE MID(CustomerName,1,1)
IN ('A','B','C')
ORDER BY CustomerName;
|
The results will be the same as when we used INSTR()
functions writing it three times:
| CustomerID | CustomerName |
| 13 | Aida Yespica |
| 6 | Anna Matias |
| 14 | Antonella Musentah |
| 3 | Barbara Spears |
| 17 | Bruce Davenport |
| 22 | Christian Barth |
|