CODEWITHSUNDEEP
sqlpostgresql
dukedexx
dukedexx

Reputation: 19

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.

Upvotes: 0

Views: 744

Answers (3)

emomaliev
emomaliev

Reputation: 2383

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

Upvotes: 0

Max
Max

Reputation: 478

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

Upvotes: 0

BeRT2me
BeRT2me

Reputation: 13242

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

Upvotes: 1

Related Questions