MariaDB incorrect syntax near ''

Question

as the title says, I have an error with my syntax somewhere. This is MariaDB 10.1.31.

DROP FUNCTION IF EXISTS NO_UMLAUT;
CREATE FUNCTION NO_UMLAUT(TextString VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    SET TextString = REPLACE(TextString, 'ä', 'a');
    SET TextString = REPLACE(TextString, 'ë', 'e');
    SET TextString = REPLACE(TextString, 'ḧ', 'h');
    SET TextString = REPLACE(TextString, 'n̈', 'n');
    SET TextString = REPLACE(TextString, 'ï', 'i');
    SET TextString = REPLACE(TextString, 'ẗ', 't');
    SET TextString = REPLACE(TextString, 'ö', 'o');
    SET TextString = REPLACE(TextString, 'ẅ', 'w');
    SET TextString = REPLACE(TextString, 'ß', 'b');
    SET TextString = REPLACE(TextString, 'ü', 'u');
    SET TextString = REPLACE(TextString, 'ẍ', 'x');
    SET TextString = REPLACE(TextString, 'ÿ', 'y');
    RETURN TextString;
END;

and the error:

 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 '' at line 3.

So far I tried a function from the documentation and there also was an error with the syntax. I am executing this query in HeidiSQL 9.5.0.5196.

score 2 · Accepted Answer · answered Apr 25 '18 at 14:43

2

I think you just need DELIMITER statements.

The function itself is fine, as shown by this SQL Fiddle (MariaDB and MySQL are the same for this purpose).

Try adding:

 DELIMITER $$

 <your function definition>

 DELIMITER ;

answered Apr 25 '18 at 14:43

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Apr 25 '18 at 14:58

A working solution:

DROP FUNCTION IF EXISTS NO_UMLAUT;

DELIMITER //
CREATE FUNCTION NO_UMLAUT(TextString VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    SET TextString = REPLACE(TextString, 'ä', 'a');
    SET TextString = REPLACE(TextString, 'ë', 'e');
    SET TextString = REPLACE(TextString, 'ḧ', 'h');
    SET TextString = REPLACE(TextString, 'n̈', 'n');
    SET TextString = REPLACE(TextString, 'ï', 'i');
    SET TextString = REPLACE(TextString, 'ẗ', 't');
    SET TextString = REPLACE(TextString, 'ö', 'o');
    SET TextString = REPLACE(TextString, 'ẅ', 'w');
    SET TextString = REPLACE(TextString, 'ß', 'b');
    SET TextString = REPLACE(TextString, 'ü', 'u');
    SET TextString = REPLACE(TextString, 'ẍ', 'x');
    SET TextString = REPLACE(TextString, 'ÿ', 'y');
    RETURN TextString;
END;
//

P.S. I would recommend to avoid using functions as it drastically decreases database performance. If your goal is performance then it would be better to perform a denormalisation of your database by creating additional field(s) with values with umlauts already removed.

It would also be better to remove umlauts in your program instead of using a mysql function as unicode can be very tricky and umlauts can be created using different approaches. I hope your are not trusting incoming data from external sources, you are sanitising it carefully and your unicode was already normalised before it was put into the database (if not - do it!).

Performance is not important in this case. There is no program, just this function + one update statement, my goal is to remove every umlaut from `first_name` column, the database is already filled. — shaelex, Apr 25 '18 at 15:02

MariaDB incorrect syntax near ''

2 Answers2

Linked