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:

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

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