Using like pattern in mysql case statement with two different tables
I have a table with names (names
). I have another that holds ids (user
).
I have a conditional construct - a case-when statement that is supposed to insert an id into the user table if a name in the names table matches a certain condition.
I have used like %
to match string patterns:
delimiter //
create procedure name_matching (in names.name varchar, ou开发者_StackOverflow中文版t id int)
begin
case
when names.name like 's%_%a' then
insert into user (id) values ('1');
else
insert into user (id) values ('2');
end case
end//
This outputs error 1064 on mysql terminal.
Is there a way to do this differently?
There are a couple of small problems with your procedure. You need a semicolon after the end case
, you need to specify a field size for the varchar
in the input parameter list, etc. The following works for me on MySQL 5.1 ("works" meaning: does not produce error when creating procedure):
delimiter $$
create procedure name_matching (in name varchar(500))
begin
case
when name like 's%_%a' then
insert into user (id) values (1);
else
insert into user (id) values (2);
end case;
end $$
精彩评论