Blanks After Function Name - SQL Server to MySQL Migration

In SQL Server you can have blank characters after the function name before parentheses. This is not allowed for some (not every) functions in MySQL and blanks must be removed.

SQL Server:

  -- A blank after the function name
  SELECT SUBSTRING ('abc', 1, 2);
  # ab  
 
  -- A newline after the function name
  SELECT SUBSTRING
    ('abc', 1, 2);
  # ab
 

MySQL:

  -- A blank after the function name
  SELECT SUBSTRING ('abc', 1, 2);
  # ERROR 1630 (42000): FUNCTION SUBSTRING does not exist. 
  # Check the 'Function Name Parsing and Resolution' section in the Reference Manual 
 
  -- A newline after the function name
  SELECT SUBSTRING
    ('abc', 1, 2);
  # ERROR 1630 (42000): FUNCTION SUBSTRING does not exist. 
 
  -- Without blanks after the function name
  SELECT SUBSTRING('abc', 1, 2);
  # ab

This also applies for some other functions:

MySQL:

  SELECT COUNT (1) FROM t;
  # ERROR 1630 (42000): FUNCTION COUNT does not exist
 
  SELECT COUNT(1) FROM t;
  # 3
 
  SELECT NOW ();
  # ERROR 1630 (42000): FUNCTION test.now does not exist.
 
  SELECT NOW();
  # 2022-12-10 12:00:27

You can change this behavior and allow blanks after the function name by adding IGNORE_SPACE option to sql_mode:

MySQL:

  -- Adding IGNORE_SPACE
  SET sql_mode=(SELECT CONCAT(@@sql_mode,',IGNORE_SPACE'));
 
  -- Now blanks are allowed
  SELECT NOW ();
  #  2022-12-10 12:31:58

Without IGNORE_SPACE option (i.e. by default), a blank is not allowed for the following functions in MySQL:

1 ADDDATE
2 BIT_AND
3 BIT_OR
4 BIT_XOR
5 CAST
6 COUNT
7 CURDATE
8 CURTIME
9 DATE_ADD
10 DATE_SUB
11 EXTRACT
12 GROUP_CONCAT
13 MAX
14 MID
15 MIN
16 NOW
17 POSITION
18 SESSION_USER
19 STD
20 STDDEV
21 STDDEV_POP
22 STDDEV_SAMP
23 SUBDATE
24 SUBSTR
25 SUBSTRING
26 SUM
27 SYSDATE
28 SYSTEM_USER
29 TRIM
30 VARIANCE
31 VAR_POP
32 VAR_SAMP

For more details see “Function Name Parsing and Resolution” section of MySQL documentation.

Also for more migration details, see SQL Server to MySQL Migration.