MySQL Creating a Function with conditional

Question

I am trying to create a mysql function with a condition that I certain word must be in the parameter for it to work

CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) 
DETERMINISTIC 
IF s = NULL 
THEN RETURN CONCAT('Hello World!')

So if the query is

SELECT hello(NULL);

Then it should output:

+--------------------+
| hello(NULL)        |
+--------------------+
| Hello Hello World! |
+--------------------+

Otherwise there shouldn't be any rows returned

When I try to do my code above, it always return a syntax error. The thing is I 'm not really good at creating mysql functions especially if there are conditionals

PS

I tried this code but I got the following error

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) 
    -> DETERMINISTIC 
    -> IF s IS NULL 
    -> THEN RETURN CONCAT('Hello World!');
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 '' at line 4

Blank · Accepted Answer · 2016-08-03T03:48:06.827

4

Your function has several syntax errors:

 DELIMITER &&

 CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) 
 DETERMINISTIC 
 BEGIN
     IF s IS NULL THEN
         RETURN CONCAT('Hello World!');
     END IF;
 END&&

 DELIMITER ;

Try to define a delimiter
Use begin, end in function body
You need end if for if clause
= null should be is null

edited Aug 03 '16 at 03:48

answered Aug 03 '16 at 02:04

Blank

12,308
1
14
32

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

You need to use IS NULL or IS NOT NULL, not the equal sign. So change your query to:

IF s IS NULL 
THEN RETURN CONCAT('Hello World!')

Please check the answer in this stackoverflow question which explains this topic.

edited May 23 '17 at 11:44

Community

1
1

answered Aug 03 '16 at 01:52

KaeL

3,639
2
28
56

I tried your suggestion.. but got an error instead.. Check my updated question – Dean Christian Armada Aug 03 '16 at 02:02

score 1 · Answer 3 · answered Aug 03 '16 at 02:06

You seem to misunderstand how functions work in the SELECT. You cannot filter rows using a function in the SELECT.

You could do something like this:

select hello(null) as flag
from dual
having flag is not null;

Note: This is a non-standard use of HAVING; it is a MySQL extension.

A function should be returning a value. So, you can write:

DELIMITER $$
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) 
DETERMINISTIC 
BEGIN
    RETURN(CASE WHEN s IS NULL 'Hello World!' END)
END;
$$
DELIMITER ;

This version explicitly returns NULL, which the HAVING clause filters out.

MySQL Creating a Function with conditional

3 Answers3