Trying to get a count of fornames which begin with eithe M or A I get right answer but is not perfect

Question

SELECT COUNT(SUBSTR(forenames,1,1))
FROM employee
WHERE
    (SUBSTR(forenames,1,1) like '%M%')
OR
    (SUBSTR(forenames,1,1) like '%A%')

Answer 1

SELECT
   COUNT(forenames)
FROM
   employee
WHERE
   forenames like 'M%'
   OR forenames like 'A%'

Answer 2

Straightforward count:

select count(*)
from   employee 
where  substr(forenames,1,1) in ('A', 'M') 
/

If you want to know number of each initial that would be :

select substr(forenames,1,1)
       , count(*)
from   employee 
where substr(forenames,1,1) in ('A', 'M') 
group by substr(forenames,1,1) 
/

Answer 3

It's not clear to me what database this is for.
That said, this is a job for regex. Performing evaluations based on the results of a function performed on a column IE:

SUBSTR(forenames, 1, 1) LIKE '%M%'

...means that if an index exists on the column - the index can't be used even if the optimizer wanted to. Wildcarding the left side in a LIKE will also stop an index from being used.

MySQL

---

Use:

SELECT COUNT(*)
  FROM EMPLOYEE e
 WHERE e.forenames REGEXP '^[AM]'

Reference:

• MySQL Documentation: 11.4.2. Regular Expressions

SQL Server

---

For SQL Server 2000, check this article regarding adding regex support. SQL Server 2005+, you have to enable CLR functions & write a CLR function that will allow you to execute regexes against column values.

SQL Server 2005+ example:

SELECT COUNT(*)
  FROM EMPLOYEE e
 WHERE dbo.RegexMatch(e.forenames, N'^[AM]') = 0

Oracle

---

Oracle 10g+ supports regular expressions - use the REGEXP_LIKE for this case:

SELECT COUNT(*)
  FROM EMPLOYEE e
 WHERE REGEXP_LIKE(e.forenames, '^[AM]')