MySQL: CONCAT_WS function is running on local but not on server.

Question

Some days ago I asked a question about my problem and I was advised to use CONCAT_WS function. I am using CONCAT_WS on my local mysql database and it is working perfectly. But it is not working on server(application hosted) and generate the following error.

FUNCTION test.CONCAT_WS does not exist

Here test in error string is my database name on server.

My query is like this:

SELECT * FROM patient WHERE CONCAT_WS (',', LastName,FirstName,BirthDate ) NOT IN ('Abdul,Quddus,2000-09-30','Wasim,Akram,1993-09-12');

Can someone tell me the problem or suggest me another solution asked in linked question above ?

Thanks

Answer 1

The easiest way to fix it is by removing the whitespace between the function name and the parenthesis, i.e. CONCAT_WS(...) instead of CONCAT_WS (...).

From the MySQL Manual:

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function.

...

You can tell the MySQL server to accept spaces after function names by starting it with the --sql-mode=IGNORE_SPACE option.

Also, this behavior depends on the MySQL version, this is why it works on one server and doesn't work on another, quote from the "Function Name Parsing and Resolution" manual page:

The number of function names affected by IGNORE_SPACE was reduced significantly in MySQL 5.1.13, from about 200 to about 30.