Problem with Create Function statement in MySQL

Question

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 need ; at the end of statements in a stored procedure or function. — Barmar
– Barmar, Commented Jan 21, 2022 at 22:41
Side note: When descval like '%CLT%' is true, so is descval like 'CLT%', descval like 'CLT%' is therefore redundant. Same for descval like 'PCB%'. — sticky bit
– sticky bit, Commented Jan 21, 2022 at 22:45

Barmar · Accepted Answer · 2022-01-21 22:46:32Z

1

You have syntax problems:

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

answered Jan 21, 2022 at 22:46

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

muncieharts Over a year ago

Not sure what I was thinking on CLT% vs %CLT% - thanks. On the query itself, I still get an error - just at an earlier spot: 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 '.id_datasource (descval varchar(100)) returns varchar(10) begin dec' at line 1

Barmar Over a year ago

Sounds like a problem with the way you entered the database name before the function name.

muncieharts Over a year ago

You nailed it. I was including database and specific table. That worked. Thanks much.

Barmar Over a year ago

BTW, you can set the default database using USE databasename;

Collectives™ on Stack Overflow

Problem with Create Function statement in MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related