Электронная почта и доменная зона

Написать запрос, который выведет список электронных адресов, у которых правильная доменная зона.

Тестовые данные:

CREATE TABLE "EMAIL"("EMAIL_NAME" VARCHAR2(4000 CHAR));

COMMENT ON TABLE "EMAIL" IS 'Адрес электронной почты';
COMMENT ON COLUMN "EMAIL"."EMAIL_NAME" IS 'Наименование';

CREATE TABLE "DOMAIN"("DOMAIN_NAME" VARCHAR2(4000 CHAR));

COMMENT ON TABLE "DOMAIN" IS 'Домен';
COMMENT ON COLUMN "DOMAIN"."DOMAIN_NAME" IS 'Наименование';

INSERT INTO domain (domain_name) VALUES ('ru');
INSERT INTO domain (domain_name) VALUES ('com');
INSERT INTO domain (domain_name) VALUES ('рф');

INSERT INTO email (email_name) VALUES ('user1@gmail.ru');
INSERT INTO email (email_name) VALUES ('user2@mail.rud778dfuds');
INSERT INTO email (email_name) VALUES ('user3@mail.com7');
INSERT INTO email (email_name) VALUES ('user4@mai4com7');
INSERT INTO email (email_name) VALUES ('user5@yandex.рф');
INSERT INTO email (email_name) VALUES ('user6@gmail.com');
/


Запрос:

SELECT e.email_name AS domain_name
  FROM email e
  JOIN domain d
    ON d.domain_name = regexp_substr(e.email_name, '[^\.]+$');
/

P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776

Tags:

Comments are closed.