Return the first and last name of customers whose email is not in the format of "<first

Hello I am new to sql and I have these question:

Return the first and last name of customers whose email is not in the format of "<first_name>.<last_name>@email.org".

and here is my code.

SELECT first_name, last_name
FROM customer
WHERE email LIKE '%<first_name>.%<last_name>%@email.org';

It returns me with 0 rows and columns where in fact all emails are in the correct format. It seems I am wrong in my 'LIKE' statement.

CodePudding user response：

Schema (PostgreSQL v13)

CREATE TABLE customer (
  "first_name" VARCHAR(6),
  "last_name" VARCHAR(5),
  "email" VARCHAR(19)
);

INSERT INTO customer
  ("first_name", "last_name", "email")
VALUES
  ('bob', 'joe', '[email protected]'),
  ('tom', 'larry', '[email protected]'),
  ('little', 'foot', '[email protected]');

Query

SELECT first_name, last_name
FROM customer
WHERE email LIKE first_name || '.' || last_name || '%';

first_name	last_name
bob	joe
tom	larry

View on DB Fiddle

CodePudding user response：

Did you try below? Also, adding a data sample would be helpful.

SELECT first_name, last_name
FROM customer
WHERE email NOT LIKE '%'||first_name||'.%'||last_name||'%@%.%';