CODEWITHSUNDEEP
sqloracle-database
MaryCoding
MaryCoding

Reputation: 664

Format result from SQL query

I am learning SQL with Oracle. I have query that shows 3 columms from a table called people: show last name, first name . I want to display another column that indicates that if there is phone number to show it and if it is null to show it as XXX-XXX-XXXX. So far my query shows the information but not in the format that i want. 

Select last_name, first_name, phone_number AS phone_Num from people

Results:

LAST_NAME        FIRST_NAME       PHONE_NUM
---------------- ---------------- ------------
Doe              Fred
Hanes            Tina             123-587-9087
Douglas          Tim
McCarthy         Bob              212-098-9876

Desired:

LAST_NAME        FIRST_NAME       PHONE_NUM    HAS_PHONE_NUM
---------------- ---------------- ------------ -------------
Doe              Fred                           XXX-XXX-XXXX
Hanes            Tina             123-587-9087  123-587-9087
Douglas          Tim                            XXX-XXX-XXXX
McCarthy         Bob              212-098-9876  212-098-9876

Upvotes: 1

Views: 136

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Use Case Statement

SELECT last_name, 
       first_name, 
       phone_number AS phone_Num, 
       CASE 
         WHEN phone_number = '' THEN 'XXX-XXX-XXXX' --or Lenght(phone_number) = 0
         ELSE phone_number 
       END          AS HAS_PHONE_NUM 
FROM   people

Upvotes: 0

Iswanto San
Iswanto San

Reputation: 18569

You can use COALESCE function:

Try this:

 Select last_name, first_name, phone_number AS phone_Num,
 coalesce(phone_number, 'XXX-XXX-XXXX') as HAS_PHONE_NUM from people

Or use NVL :

 Select last_name, first_name, phone_number AS phone_Num,
 nvl(phone_number, 'XXX-XXX-XXXX') as HAS_PHONE_NUM from people

COALESCE is a part of ANSI-92 standard and NVL is Oracle specific.

Upvotes: 1

Related Questions