1

I have an assignment where I have a table of "suppliers" and each supplier comes with a description. Some descriptions provide an email address while the rest do not. I need to display JUST the email provided, and if there isn't one I need to display "No Email Provided". I was easily able to extract the email addresses, but I can't figure out how to replace all the empty slots with the requested message.

I have tried several, probably very wrong, methods:

Attempt 1:

SELECT REGEXP_SUBSTR(description, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}') OR 'No email provided' "Supplier emails" FROM suppliers;

Attempt 2: (Note: I expected this to not work properly but decided to give it a shot anyway out of lack of other ideas)

SELECT REGEXP_SUBSTR(description, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}') || 'No email provided' "Supplier emails" FROM suppliers;

Attempt 3:

SELECT REGEXP_SUBSTR(description, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}', 'No email provided') "Supplier emails" FROM suppliers;

Attempt 4:

SELECT REGEXP_SUBSTR(description, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}') "Supplier emails" FROM suppliers;
SELECT REPLACE('-', "No Email Provided") FROM suppliers;

I have tried looking this up on Google with variations in the wording, and the textbook I have doesn't clearly explain this specific situation.

This is the table I am using, for context about the results:

INSERT INTO suppliers
  VALUES ('Second Hand Reads', 
         'wholesaler of used books only, located in Chicago, 634.555-8787');
INSERT INTO suppliers
  VALUES ('Leftovers', 
     'Physical store location, located in Seattle, willing to fill referred sales, sales@leftovers.com');
INSERT INTO suppliers
  VALUES ('Read Again', 
 'Chain of used book store fronts, seeking online sales partner, located in western U.S., 919-555-3333');
INSERT INTO suppliers
  VALUES ('Bind Savers', 'Used book wholsaler, stock includes international titles, 402-555-2323');
INSERT INTO suppliers
  VALUES ('Book Recyclers', 'Used book chain, located in Canada, large volume of sales, 888.555.5204');
INSERT INTO suppliers
  VALUES ('Page Shock', 'Book wholsaler for specialty books and graphic novels, help@pageshock.com');
INSERT INTO suppliers
  VALUES ('RePage', 'Used book vendor, only wholesales, Wash D.C., 555-0122');
COMMIT;

The expected output is

                           Supplier emails
------------------------------------------------------------------
No email provided
--------------------
sales@leftovers.com
--------------------
No email provided
--------------------
No email provided
--------------------
No email provided
--------------------
help@pageshock.com
--------------------
No email provided

But the output was either an error or the one I expected not to work

                           Supplier emails
------------------------------------------------------------------
No email provided
-------------------------------------
sales@leftovers.comNo email provided
-------------------------------------
No email provided
-------------------------------------
No email provided
-------------------------------------
No email provided
-------------------------------------
help@pageshock.comNo email provided
-------------------------------------
No email provided
user147219
  • 355
  • 1
  • 4
  • 13

1 Answers1

3

You need to use either the NVL or COALESCE functions:

SELECT STORE_NAME,
       DESCRIPTION,
       NVL(REGEXP_SUBSTR(description, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}'),
           'No email provided') AS EMAIL
  FROM SUPPLIERS

dbfiddle here

NVL accepts two arguments. If the first argument is not NULL it returns it; otherwise it returns the second argument.

COALESCE is similar to NVL, but it accepts as many arguments as you care to supply and returns the first one which is not NULL. If all arguments are NULL COALESCE will return NULL.

Best of luck.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110