SQL Syntax Issue in Moving to MariaDB Environment

Question

I am updating some SQL syntax because we're moving from a Sybase/SQL Anywhere environment to MariaDB. One of the queries I'm running into a problem with is this one:

UPDATE
  DBA_clients
SET
  DBA_clients.patient_firstname = Trim([patient_firstname]),
  DBA_clients.patient_lastname = Trim([patient_lastname])
WHERE
  (((DBA_clients.patient_firstname) Like "* *"))
  OR (((DBA_clients.patient_lastname) Like "* *"));

Specifically the syntax error is with [patient_firstname] and [patient_lastname]

I've tried various changes, including placing these column names within quotes. What is the syntax issue here, and how can I resolve it?

Answer 1

Remove the escape characters for the identifiers:

UPDATE DBA_clients
    SET patient_firstname = Trim(patient_firstname),
        patient_lastname = Trim(patient_lastname)
    WHERE patient_firstname Like '* *' OR
          patient_lastname Like '* *';

The identifiers are fine. They don't need to be escaped.

I removed the table references because the query is only referring to one table -- and the mechanism for including shorter table aliases differs in the two databases.

I also assume that you are looking for spaces in the names, rather than an exact match to '* *'. If so, the correct wildcard (in both SQL Server and MariaDB) is '%':

UPDATE DBA_clients
    SET patient_firstname = Trim(patient_firstname),
        patient_lastname = Trim(patient_lastname)
    WHERE patient_firstname Like '% %' OR
          patient_lastname Like '% %';