SQL Replace NULL Values with Own text

Question

I need to display the keyboard players from a list of bands, and I've been able to using the following SQL:

SELECT BAND.NAME AS Band_Name, KBPLAYER.NAME AS Keyboard_Player
FROM BAND
FULL OUTER JOIN (
    SELECT M.NAME, MO.BID
    FROM MEMBEROF MO, MEMBER M
    WHERE MO.INSTRUMENT='keyboards'
    AND M.MID=MO.MID
    ) KBPLAYER
ON BAND.BID=KBPLAYER.BID
ORDER BY BAND.NAME, KBPLAYER.NAME

The above query displays the names of all the band and the keyboard player (if any) in that band, but I also want to display 'No KeyBoard Players' for those bands that don't have a keyboard player. How can I achieve this? Please let me know if you need me to furnish with details of the table structure.

Update: Please note that I'm not able to use any of the SQL3 procedures (COALESCE, CASE, IF..ELSE). It needs to conform strictly to SQL3 standard.

It is not showing NULL; just an empty field, indicating it is null. — Kuan E.
– Kuan E., Commented Feb 5, 2014 at 6:50

harmic · Accepted Answer · 2014-02-05 06:44:06Z

31

Use the coalesce function. This function returns the first of it's arguments that are not null. Eg:

COALESCE(KBPLAYER.NAME,'No KeyBoard Players') AS Keyboard_Player

answered Feb 5, 2014 at 6:44

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kuan E. Over a year ago

That works - except part of my assignment's requirement is that I'm not able to use COALESCE. Is there an alternative that I can look into?

harmic Over a year ago

Are you allowed to use a case statement? And please update your question with any constraints like functions you are not allowed to use

Kuan E. Over a year ago

Updated. Please refer to above.

Kuan E. Over a year ago

Thanks for introducing to me COALESCE, this will come useful in another one of my assignments.

Kuan E. · Accepted Answer · 2014-02-05 07:31:02Z

3

I've decided to do it differently since I wasn't going anywhere with the above SQL. I'll appreciate if anyone has suggestions to make for the above SQL with the set constraints.

SELECT 
  band.name AS Band_Name, 'NULL' AS Keyboard_Player
FROM 
  memberof
INNER JOIN 
 member
ON 
  memberof.mid = member.mid 
FULL JOIN 
  band
ON 
  memberof.bid = band.bid 
AND 
  instrument = 'keyboards'

WHERE  
  member.name IS NULL 

UNION

SELECT 
  band.name AS Band_Name, member.name AS Keyboard_Player
FROM 
  memberof
INNER JOIN 
 member
ON 
  memberof.mid = member.mid 
FULL JOIN 
  band
ON 
  memberof.bid = band.bid 
WHERE
  instrument = 'keyboards'

answered Feb 5, 2014 at 7:31

Kuan E.

3831 gold badge3 silver badges16 bronze badges

Comments

Naveen · Accepted Answer · 2014-02-05 06:54:30Z

1

As per your comment to replace empty string to some text, the simple solution is to use case statement like below.

SELECT BAND.NAME AS Band_Name, 
CASE WHEN KBPLAYER.NAME = '' THEN 'No Player' ELSE KBPLAYER.NAME END AS Keyboard_Player
FROM BAND
FULL OUTER JOIN (
    SELECT M.NAME, MO.BID
    FROM MEMBEROF MO, MEMBER M
    WHERE MO.INSTRUMENT='keyboards'
    AND M.MID=MO.MID
    ) KBPLAYER
ON BAND.BID=KBPLAYER.BID
ORDER BY BAND.NAME, KBPLAYER.NAME

answered Feb 5, 2014 at 6:54

Naveen

1,5021 gold badge15 silver badges24 bronze badges

1 Comment

Kuan E. Over a year ago

Thanks Naveen - but I've just updated my question with specific constraints, as advised by Harmic.

tarzanbappa · Accepted Answer · 2014-02-05 06:58:06Z

0

Try this....

    SELECT BAND.NAME AS Band_Name, 
ISNULL(NULLIF(KBPLAYER.NAME,''),'No KeyBoard Players') AS Keyboard_Player
    FROM BAND
    FULL OUTER JOIN (
        SELECT M.NAME, MO.BID
        FROM MEMBEROF MO, MEMBER M
        WHERE MO.INSTRUMENT='keyboards'
        AND M.MID=MO.MID
        ) KBPLAYER
    ON BAND.BID=KBPLAYER.BID
    ORDER BY BAND.NAME, KBPLAYER.NAME

answered Feb 5, 2014 at 6:58

tarzanbappa

4,98623 gold badges79 silver badges121 bronze badges

4 Comments

Kuan E. Over a year ago

I'm getting ERROR: function isnull(text, unknown) does not exist'.

Naveen Over a year ago

ISNULL is checking if the value of column is NULL, but in this case it has empty string. So Is null is not going to work.

tarzanbappa Over a year ago

Because of that, I've added the NULLIF see above query

Naveen Over a year ago

Oh yeah.. I overlooked into it, its fine. :)

JustBeingHelpful · Accepted Answer · 2022-02-16 20:24:15Z

0

The above solutions will not handle an empty string. This one will.

COALESCE(NULLIF(KBPLAYER.NAME,''),'No KeyBoard Players') AS Keyboard_Player

answered Feb 16, 2022 at 20:24

JustBeingHelpful

19.1k39 gold badges169 silver badges254 bronze badges

Comments

Ram Dwivedi · Accepted Answer · 2014-02-05 06:59:10Z

-1

If NVL is allowed, below should work.

NVL(KBPLAYER.NAME, 'No KeyBoard Players')

answered Feb 5, 2014 at 6:59

Ram Dwivedi

4703 silver badges11 bronze badges

2 Comments

Kuan E. Over a year ago

I'm getting 'ERROR: function nvl(character varying, unknown) does not exist'.

harmic Over a year ago

The question is tagged postgresql - NVL is non-standard and not supported by postgresql. coalesce is the equivalent.

Collectives™ on Stack Overflow

SQL Replace NULL Values with Own text

6 Answers 6

4 Comments

Comments

1 Comment

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

1 Comment

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related