How to use a case statement in mysql function

Question

In know MySQL (MariaDB) functions and the case statement but how to combine the two? I'm implementing a TO_STR translate function where I don't want to pollute the database with addtional tables. This is what I tried:

CREATE FUNCTION CODE_TO_STR(CODE CHAR(4)) RETURNS VARCHAR(50) AS
BEGIN
RETURN SELECT
  CASE CODE
  WHEN '1' THEN 'Code is one'
  WHEN '2' THEN 'Code is two'
  ELSE CODE
  END;
END;

which gives an 1064 error.

EDIT: corrected the error RETURN->RETURNS

EDIT: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT CASE CODE WHEN '1' THEN 'Code is one' WHEN '2' THEN 'Code is two...' at line 3 0.000 sec

Answer 1

The SELECT is superfluous. the correct CREATE should be:

DELIMITER @@
CREATE FUNCTION CODE_TO_STR(CODE CHAR(4)) RETURNS VARCHAR(50)
BEGIN
RETURN
  CASE CODE
  WHEN '1' THEN 'Code is one'
  WHEN '2' THEN 'Code is two'
  ELSE CODE
  END;
END@@

Answer 2

(@theking2 gives a straightforward answer. I am giving a more general answer.)

• The RETURN of a FUNCTION needs an "expression".
• A SELECT returning a single value can be either a "statement" or an "expression". But...
• When used as "expression", a SELECT statement must be surrounded by parentheses.