How to put quotes in a variable for MySQL query

Question

How to put quotes in a variable for MySQL query

Navigation

#1 by (5 votes)

3

I'm doing a procedure, I need to put a varchar variable in the query, but it does not go with the single quotation marks, I've tried concatenating it or something like that, but it's no use:

set @comando := concat(@comando, '"'); //ao contrário tbm
set @comando := concat(@comando, classe);
set @comando := concat(@comando, '"');

I've already put triple quotation marks too:

set @comando := concat(@comando, '''classe''');

What better way to concatenate or make MySQL recognize as a varchar?

Code:

delimiter $$
create procedure eixos_caminhao (in classe varchar(3))
begin
    set @comando := 'select count(*) as qtdeCaminhoes';
    set @comando:= concat(@comando, ' from tb_vbv where classe = ');
    set @comando := concat(@comando, classe);

    PREPARE myquery FROM @comando;
    EXECUTE myquery;
end $$
delimiter;

mysql sql stored-procedures

asked by anonymous 09.10.2018 / 21:33

1 answer

Alternative image in case of load failure Disable tab that shows all results

score 5 · Accepted Answer

Whenever we are setting a string and within that string it is necessary to have another string , we have some outputs:

Since no SQL both ' and " start and end string , we can interleave these characters to form a string within the other:

set @comando = 'select count(*) as qtdeCaminhoes';
set @comando = concat(@comando, ' from tb_vbv where classe = "');
set @comando = concat(@comando, classe);
set @comando = concat(@comando, '"');

The result would be:

select count (*) as qtdeCamines from tb_vbv where class="class_content"

But if you really want to bypass string with ' , you can use the previous inverse method ( "'" ), or use \ which is an escape character, ' or " after it, will not be considered as closing a string :

set @comando = 'select count(*) as qtdeCaminhoes';
set @comando = concat(@comando, ' from tb_vbv where classe = \'');
set @comando = concat(@comando, classe);
set @comando = concat(@comando, '\'');

Another information about the CONCAT function is that it accepts several parameters to concatenate at one time, its code could be summarized for this example:

set @comando = 'select count(*) as qtdeCaminhoes';
set @comando = concat(@comando, ' from tb_vbv where classe = ');
set @comando = concat(@comando, '\'', classe, '\'');