Blanks After Function Name - SQL Server to MariaDB 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 MariaDB 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
 

MariaDB:

  -- 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:

MariaDB:

  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 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:

MariaDB:

  -- 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 MariaDB:

For more details, see SQL Server to MariaDB Migration.