MySQL Using a Variable LIKE '%Variable_Name%' in a trigger

Question

I have this little simple trigger..

BEGIN

    DECLARE FILE_NAME VARCHAR(250);
    DECLARE FILE_REFR VARCHAR(500);


SET FILE_NAME = 'Foo';
SET FILE_REFR = 'Bar';

--- I'd like to execute the next statement, using variable FILE_REFR between %% in a LIKE clause:
SELECT COUNT(*) INTO @num_rows FROM referers WHERE filename = FILE_NAME AND ref NOT LIKE "%FILE_REFR%";

...
...
...

END

Unfortunately, the variable name is not being picked up as a variable.. but as a CHAR, I know there is something missing there.

Help is more than appreciated.. :)

Aman Aggarwal · Accepted Answer · 2014-05-26 05:48:55Z

2

Use the variable as:

CONCAT('%', FILE_REFR, '%');

So the complete select query is:

SELECT COUNT(*) INTO @num_rows FROM referers WHERE filename = FILE_NAME AND ref NOT LIKE  CONCAT('%', FILE_REFR, '%');

Thanks

answered May 26, 2014 at 5:48

Aman Aggarwal

18.6k10 gold badges55 silver badges84 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2014-05-26 02:14:32Z

1

Is this what you want?

SELECT COUNT(*) INTO @num_rows
FROM referers
WHERE filename = FILE_NAME AND
      ref NOT LIKE CONCAT('%', FILE_REFR, '%');

MySQL does not find variables inside string literals. Instead you have to use CONCAT() to piece the pattern together.

answered May 26, 2014 at 2:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL Using a Variable LIKE '%Variable_Name%' in a trigger

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related