开发者

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 $$
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜