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-03 03:48:06Z

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 3, 2016 at 3:48

answered Aug 3, 2016 at 2:04

Blank

12.4k1 gold badge16 silver badges34 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:44:20Z

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, 2017 at 11:44

CommunityBot

11 silver badge

answered Aug 3, 2016 at 1:52

KaeL

3,6612 gold badges31 silver badges59 bronze badges

1 Comment

Dean Christian Armada Over a year ago

I tried your suggestion.. but got an error instead.. Check my updated question

Gordon Linoff · Accepted Answer · 2016-08-03 02:06:09Z

1

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.

answered Aug 3, 2016 at 2:06

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL Creating a Function with conditional

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related