views_query_alter apply “%LIKE%” condition

You can do it using code similar to the following one.

function mymodule_views_query_alter(&$view, &$query) {
  if (!empty($_GET['query'])) {
    $query->add_where(0, 'title', $_GET['query'], 'LIKE');
  }
}

The first argument that is required from $query->add_where() is the group for the WHERE to add. The documentation says to pass 0 as argument, to use the default one.

The code I shown works if $_GET['query'] contains an SQL regular expression. If $_GET['query']contains a string that is not a regular expression, and that is used to build the regular expression for example as '%' . $_GET['query'] . '%', the code to use should be the following one.

function mymodule_views_query_alter(&$view, &$query) {
  if (!empty($_GET['query'])) {
    $query->add_where(0, 'title', '%' . db_like($_GET['query']) . '%', 'LIKE');
  }
}

Notice what reported in the documentation for views_plugin_query_default::add_where():

The caller is responsible for ensuring that all fields are fully qualified (TABLE.FIELD) and that the table already exists in the query.