How to extract the domain name from an email address

Method 1:

SELECT SUBSTR(
'EXAMPLE@YAHOO.COM' ,
INSTR('EXAMPLE@YAHOO.COM' , '@' ) + 1 )
AS EMAIL_DOMAIN
FROM DUAL;

Method 2:

SELECT
REPLACE(
REGEXP_SUBSTR( 'EXAMPLE@YAHOO.COM' , '@.*' ) , '@' )
AS EMAIL_DOMAIN
FROM DUAL;

All you need to do now is to replace ‘EXAMPLE@YAHOO.COM’ with your email address column name or value.

 

How to denormalize a table in oracle.

Let us first begin by creating a table and setting up some data.

Topic 1:
Customer and Brand are a one to one relationship.

 CREATE TABLE CUSTOMER_BRAND
(
CUSTOMER_ID NUMBER(10),
BRAND VARCHAR2(20)
);
INSERT INTO CUSTOMER_BRAND VALUES( 1 , 'BMW' );
INSERT INTO CUSTOMER_BRAND VALUES( 1, 'CHEVY' );
INSERT INTO CUSTOMER_BRAND VALUES( 1, 'FORD' );
INSERT INTO CUSTOMER_BRAND VALUES( 2 , 'BMW' );
INSERT INTO CUSTOMER_BRAND VALUES( 2 , 'FORD');
INSERT INTO CUSTOMER_BRAND VALUES( 3 , 'CHEVY');

Please note that a customer can only have one brand.

Now let’s try to view the data in the table.

SELECT * FROM CUSTOMER_BRAND;

CUSTOMER_BRAND

CUSTOMER_IDBRAND
1BMW
1CHEVY
1FORD
2BMW
2FORD
3CHEVY

Now we would like to flatten the data (aka pivot).

SELECT DISTINCT CUSTOMER_ID ,
(
CASE
WHEN (SELECT 'Y'
FROM CUSTOMER_BRAND A
WHERE BRAND       = 'BMW'
AND A.CUSTOMER_ID = B.CUSTOMER_ID ) = 'Y'
THEN 'Y'
ELSE 'N'
END) AS BMW ,
(
CASE
WHEN (SELECT 'Y'
FROM CUSTOMER_BRAND A
WHERE BRAND       = 'FORD'
AND A.CUSTOMER_ID = B.CUSTOMER_ID) = 'Y'
THEN 'Y'
ELSE 'N'
END) AS FORD,
(
CASE
WHEN (SELECT 'Y'
FROM CUSTOMER_BRAND A
WHERE BRAND       = 'CHEVY'
AND A.CUSTOMER_ID = B.CUSTOMER_ID ) = 'Y'
THEN 'Y'
ELSE 'N'
END) AS CHEVY
FROM CUSTOMER_BRAND B;

Results:

CUSTOMER_IDBMWFORDCHEVY
1YYY
3NNY
2YYN