CODEWITHSUNDEEP
mysqlsqlconcatenation
k102
k102

Reputation: 8089

if inside concat

Here's the code I'm trying to use to create a trigger:

-- Dumping structure for trigger kavanga_lead.click_links_insert
DROP TRIGGER IF EXISTS `click_links_insert`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `click_links_insert` AFTER INSERT ON `click_links` FOR EACH ROW BEGIN
    INSERT INTO actions_log 
        (
            `uid`,
            `table`,
            `action`,
            `new`
        )
        VALUES
        (  
            @user_id,
            'click_links',
            'insert',
            concat(NEW.id ,'|', NEW.`contents`, '|', NEW.channel_id, '|', NEW.name, '|', NEW.hidden, '|', if(NEW.prefix_id is null,'',NEW.prefix_id) '|', if(NEW.postfix_id is null,'',NEW.postfix_id))
        );
END//
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;

I keep getting an error (1583) Incorrect parameters in the call to concat. It works well if I don't use if(NEW.prefix_id is null,'',NEW.prefix_id) but just a NEW.prefix_id. But this field can be null, so all concat's result becomes null to.

So, the question is how can I use IF staments inside CONCAT call?

Upvotes: 5

Views: 7374

Answers (3)

Peter Lang
Peter Lang

Reputation: 55594

You're missing a comma after your IF. Once this is fixed, it would be better to use COALESCE instead (or IFNULL if you prefer):

COALESCE(NEW.prefix_id,  '')

Upvotes: 2

John Woo
John Woo

Reputation: 263893

your query is not valid because of the missing comma after your inline IF statement. 

concat(NEW.id ,'|', NEW.`contents`, '|', 
       NEW.channel_id, '|', NEW.name, '|', NEW.hidden, '|', 
       if(NEW.prefix_id is null,'',NEW.prefix_id) '|', 
       --                                         ^ error here
       if(NEW.postfix_id is null,'',NEW.postfix_id))

it must be

concat(NEW.id ,'|', NEW.`contents`, '|', 
       NEW.channel_id, '|', NEW.name, '|', NEW.hidden, '|', 
       if(NEW.prefix_id is null,'',NEW.prefix_id), '|', 
       if(NEW.postfix_id is null,'',NEW.postfix_id))

Upvotes: 0

Omesh
Omesh

Reputation: 29121

that's because you forgot one comma after if(NEW.prefix_id is null,'',NEW.prefix_id) try:

concat(NEW.id ,'|', NEW.`contents`, '|', NEW.channel_id, '|', NEW.name, '|', NEW.hidden, '|', if(NEW.prefix_id is null,'',NEW.prefix_id), '|', if(NEW.postfix_id is null,'',NEW.postfix_id))

Upvotes: 6

Related Questions