PHP/MySQL ->
MySQL string functions. Part 3

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:
CustomerIDCustomerName
13Aida Yespica
6Anna Matias
14Antonella Musentah
3Barbara Spears
17Bruce Davenport
22Christian 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:
CustomerIDCustomerName
13Aida Yespica
6Anna Matias
14Antonella Musentah
3Barbara Spears
17Bruce Davenport
22Christian Barth




sqlexamples.info