I am attempting to create a function in MySQL that will return a value based on the presence of specific strings in a field. My select statement will ultimately need to make this evaluation many times so I wanted to create a function for efficiency purposes. Here is my code:
create function [dbname].id_datasource
(descval varchar(100))
returns varchar(10)
begin
declare datasource varchar(10) default ''
select datasource =
case when descval like 'CLT%' or descval like '%CLT%' then 'CLT'
when descval like 'PCB%' or descval like '%PCB%' then 'PCB'
else 'NA' end
return (datasource)
end
I get this error:
SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select datasource = case when descval like 'CLT%' or descval like '%CLT%' th' at line 6
note: it requires me to enter a db name prefix for my function - it returns 'no database selected' without it - I did substitute my long database name for [dbname] above.
You have syntax problems:
;
at the end of each statement.SET
to assign a variable.DELIMITER $$
create function [dbname].id_datasource (descval varchar(100))
returns varchar(10)
begin
declare datasource varchar(10) default '';
SET datasource =
case
when descval like '%CLT%' then 'CLT'
when descval like '%PCB%' then 'PCB'
else 'NA'
end;
return datasource;
end
$$
DELIMITER ;
There's no need to have both LIKE 'CLT%'
and LIKE '%CLT%'
, since anything that matches CLT%
will also match %CLT%
.