Question 112770 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Need query result that doesn't start and end with "A" or "a" letter

I am trying to write a query that returns list of customer names that do not start or end with letter "A" (either in upper case or lower case). Following is my query,

SELECT * FROM Customers 
WHERE 
CustomerName IN (SELECT * FROM Customers WHERE CustomerName NOT LIKE 'A%') 
AND 
CustomerName IN (SELECT * FROM Customers WHERE CustomerName NOT LIKE '%a')

In the query result, I am getting an "Error Code: 1241. Operand should contain 1 column(s)". Columns of Customers table is

`customers` (
  `CustomerID` int(11) NOT NULL,
  `CustomerName` varchar(255) DEFAULT NULL,
  `ContactName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  `PostalCode` varchar(255) DEFAULT NULL,
  `Country` varchar(255) DEFAULT NULL

Can someone please correct my query?

Tags:

3

Answers


  1. 0

    Please try this way, It will take care of case sensitive as well.

    SELECT * FROM customers WHERE CustomerName  NOT LIKE 'A%'  AND CustomerName  NOT LIKE '%A'

    You don’t need to perform subquery for this.

    Login or Signup to reply.
  2. 0 
    SELECT CustomerName FROM Customers 
WHERE 
CustomerName NOT LIKE 'A%'
AND 
CustomerName NOT LIKE '%a'

    you dont need subquery.

    if you want to use subquery you code should like bellow,

    SELECT * FROM Customers 
WHERE 
CustomerName IN (SELECT CustomerName FROM Customers WHERE CustomerName NOT LIKE 'A%') 
AND 
CustomerName IN (SELECT  CustomerName FROM Customers WHERE CustomerName NOT LIKE '%a')

    "But in here you dont need that.

    "So you should define in subquery what you want after the SELECT.

    Login or Signup to reply.
  3. 0

    Using a sub query adds is like adding extra calculation which will utilize more resources.

    This can be achieved using a simple where clause like this:

    select
    *
from
    Customers
where
    upper(CustomerName) not like 'A%'
    and upper(CustomerName) not like '%A'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search