mysqlcreate-function

Problem with Create Function statement in MySQL


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.


Solution

  • You have syntax problems:

    1. Missing ; at the end of each statement.
    2. Use 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%.