1

I am trying to create a string out of several fields, some of which can be NULL. If I do

Select Concat(FieldA,IsNULL(FieldB,''),FIeldC) from Table

I get

1582 - Incorrect parameter count in the call to native function 'IsNULL'

If I do

Select Concat(FieldA,Coalesce(FieldB,''),FIeldC) from Table

I get NULL records where FieldB is NULL I even tried for the heck of it:

 Select Concat(FieldA,IFNULL(FieldB,''),FIeldC) from Table

and got empty records again

Perhaps I'm misunderstanding but I thought the whole point was to return either the record value or an empty value on NULL records to avoid null returns

user3649739
  • 1,829
  • 2
  • 18
  • 28
  • In some databases the empty string is considered NULL. Try to change '' to 'test' and see what happens. – ewramner Aug 02 '20 at 17:24
  • 1
    In MySql ISNULL() takes only 1 argument: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_isnull If your last 2 queries return null then check also the columns FieldA and FieldC for nulls. – forpas Aug 02 '20 at 17:29
  • @forpas Thanks, however now instead of error message it returns NULL records where FIeldB is NULL – user3649739 Aug 02 '20 at 17:37
  • @ewramner Tried that with `Coalesce` since `IsNULL() ` only allows the field parameter and still got NULL records. – user3649739 Aug 02 '20 at 17:38
  • 1
    Concat() returns null only if any of its arguments is null. So you must have nulls in the other columns. – forpas Aug 02 '20 at 17:42
  • @forpas Ok my bad there were three places in the actuall query (long one) where the FieldB was called thus is appeared even with the fix it was returning `NULL` records. I fixed each one to do just `IsNUL(FIeldB)` and it works fine. Thanks, upvoted, feel free to make official answer I'll accept it. – user3649739 Aug 02 '20 at 17:46
  • Fine if it is solved. – forpas Aug 02 '20 at 17:46
  • Provide an example online fiddle based on your structure and data. – Akina Aug 02 '20 at 18:04
  • @forpas FWIW, the IsNULL returned ' 0 ' which doesn't work for me. I saw a bunch of solutions using `CAST` however to simplify (for me at least) I just wrapped the `Concat` inside a `Replace` so e.g. `Concat(FieldA,IsNULL(FIeldB))` became `Replace(Concat(FieldA,IsNULL(FIeldB)),' 0 ','')` – user3649739 Aug 02 '20 at 19:26
  • Don't use ISNULL() because it only returns 1 or 0. Use COALESCE(FIeldB, ''). – forpas Aug 02 '20 at 19:27
  • 1
    @forpas Yeah I just found that ou the hard way :) thanks – user3649739 Aug 02 '20 at 19:46

0 Answers0