Returning null or empty string in mysql statement

Question

A table I need to run a query on has fields that can have a NULL value, an empty string, or some text, number, etc.

So for instance I have a table "data" with fields id, field1, field2, field3, field4.

The id is the auto-incremented key, so that will have a value, but the other three fields can have any of the above-mentioned instances.

So the result of of a query on the table could be id=1, field1=NULL, field2='', field3='some text', field4='5'.

How, in the SELECT statement can I return an empty string for each field that is NULL?

Thanks!

[Additional info]

I forgot to mention this until after I already got some replies, so I'm not sure if it'll change the answer.

Also, some of the fields in the table are an ID from another table, so I'm doing inner joins, but there may or may not be a value in an id field in the "data" table.

Using the "data" table from above, if field4 were 5 (and not '5' as originally defined above) and was an id for the table "name", I'd have a join that looked something like:

INNER JOIN name n ON n.id = data.field4

I believe it is the joins that are preventing some rows from returning in my result set. How do I handle that?

Thanks and sorry if anyone already answered.

Salil · Accepted Answer · 2013-01-18 11:21:06Z

2

Use IFNULL

SELECT id, IFNULL(field1,''), IFNULL(field2,''), IFNULL(field3,'')
FROM data

answered Jan 18, 2013 at 11:21

Salil

47.7k22 gold badges127 silver badges161 bronze badges

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

1 Comment

marky Over a year ago

As you can see I added some info after you posted this answer. How do I handle the joins when one record may have a null for the name ID in field4, but another record does have a value in field4. I'm only getting back records that have a value in field4, so I'm assuming the records with null in that field won't work with the join, so they're not in the result set.

John Woo · Accepted Answer · 2013-01-18 11:19:45Z

0

SELECT id, COALESCE(field1,''), COALESCE(field2,''), COALESCE(field3,'')
FROM   data

answered Jan 18, 2013 at 11:19

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

m0skit0 Over a year ago

How [...] can I return an empty string for each field that is NULL?

marky · Accepted Answer · 2013-01-18 19:09:34Z

0

LEFT JOIN - that's the answer.

I just changed my INNER JOINS to LEFT JOINS and now I'm getting all of the rows, including the ones where the id fields are empty.

Of course, the info I provided before the "[Additional info]" in my original question wasn't the whole story. Sorry, guys, my bad. Salil answered correctly based on the info I originally provided.

answered Jan 18, 2013 at 19:09

marky

5,08819 gold badges65 silver badges110 bronze badges

Collectives™ on Stack Overflow

Returning null or empty string in mysql statement

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related