MySql Query Replace NULL with Empty String in Select

Question

How do you replace a NULL value in the select with an empty string  It doesnt look very professional to output  NULL  values   This is very unusual and based on my syntax I would expect it to work  Hoping for an explanation why it doesnt   select CASE prereq WHEN  prereq IS NULL  THEN     ELSE prereq end from test    Example of what the original table looks like  what I want  and what actual prints   original     wanted      what actually prints --------     ------      --------------------- value1       value1       NULL                     NULL value2       value2       NULL                     NULL   As you can see it does the opposite of what I want  hence I tried flipping the IS NULL to IS NOT NULL and of course that didnt fix it  also tried swapping the position of when case  which did not work   Edit  It seems the 3 solutions given below all do the task   regards  select if prereq IS NULL      prereq   from test select IFNULL prereq     from test select coalesce prereq      from test

User · Answer

Try this  this should also get rid of those empty lines also   SELECT prereq FROM test WHERE prereq IS NOT NULL

User · Answer

SELECT COALESCE prereq      FROM test   Coalesce will return the first non-null argument passed to it from left to right  If all arguemnts are null  it ll return null  but we re forcing an empty string there  so no null values will be returned   Also note that the COALESCE operator is supported in standard SQL  This is not the case of IFNULL  So it is a good practice to get use the former  Additionally  bear in mind that COALESCE supports more than 2 parameters and it will iterate over them until a non-null coincidence is found

User · Answer

I have nulls  NULL as default value  in a non-required field in a database  They do not cause the value  null  to show up in a web page  However  the value  null  is put in place when creating data via a web page input form  This was due to the JavaScript taking null and transcribing it to the string  null  when submitting the data via AJAX and jQuery  Make sure that this is not the base issue as simply doing the above is only a band-aid to the actual issue  I also implemented the above solution IFNULL      as a double measure  Thanks

User · Answer

select IFNULL  prereq      as ColumnName FROM test   this query is selecting  prereq  values and if any one of the values are null it show an empty string as you like So  it shows all values but the NULL ones are showns in blank

User · Answer

Some of these built in functions should work   Coalesce Is Null IfNull

User · Answer

If you really must output every values including the NULL ones   select IFNULL prereq     from test

User · Answer

Try below        select if prereq IS NULL      prereq   from test

User · Answer

Try COALESCE   It returns the first non-NULL value   SELECT COALESCE  prereq        FROM  test

User · Answer

UPDATE your table set your field    where your field is null

User · Answer

The original form is nearly perfect  you just have to omit prereq after CASE   SELECT   CASE     WHEN prereq IS NULL THEN         ELSE prereq   END AS prereq FROM test

User · Answer

I know this is old question but i got best solution without change query and also support for SELECT   statement foreach   row as  amp  value      if  value  null         value  quot  quot

[mysql] MySql Query Replace NULL with Empty String in Select

Examples related to mysql

Examples related to string

Examples related to replace

Examples related to null