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

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.

sqlpostgresql


Solution 1:[1]

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

Solution 2:[2]

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||'%@%.%';

Solution 3:[3]

You can also use a regular expression:

-- Any lines where email = first_name.last_name@
        
  SELECT first_name, last_name FROM customer WHERE email ~ format('[%s]+[.]+[%s]+@',first_name,last_name)
    

-- Any lines where email = *.*@
    
 SELECT first_name, last_name FROM customer WHERE email ~ '[\w]+[.]+[\w]+@'

Result:

first_name last_name
bob joe
tom larry

Demo in DBfiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 BeRT2me
Solution 2 Max
Solution 3

Related Questions