skip to Main Content

I am using multiple concat with like but I am not getting all the output from the table. I am using below SQL query below in the database.

  select 
  company_id as ID, 
  concat(
    c.list_rank, '|||', c.company_name, 
    '|||', c.company_size, '|||', c.industry
  ) as post_content, 
  c.admin_approved from company22 c where company_name like ' % XYZ % ';

I am getting below output. Post content is showing null but I have all the data in the table

enter image description here

2

Answers


  1. At least one of the values you’re concatenating must be NULL. Since any operation involving NULL returns NULL, that makes the final result NULL.

    Use IFNULL() to replace the null values with an empty string.

    CONCAT_WS('|||', IFNULL(c.list_rank, ''), IFNULL(c.company_name, ''), IFNULL(c.company_size, ''), IFNULL(c.industry, '')) AS post_content
    

    I also use CONCAT_WS() to simplify joining several values with the same delimiter.

    Login or Signup to reply.
  2. you can also use CONCAT_WS

    like

     concat_ws('',
        c.list_rank, '|||', c.company_name, 
        '|||', c.company_size, '|||', c.industry
      ) as post_content
    

    The separator can be a string, as can the rest of the arguments. If the separator is NULL , the result is NULL ; all other NULL values are skipped. This makes CONCAT_WS() suitable when you want to concatenate some values and avoid losing all information if one of them is NULL.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search