How to properly skip apostrophe in db_select? In my mysql database I have texts full of apostropes which are used as stress marks. I need to implement keyword search ignoring these marks.

I tested it first by using direct sql in PhpMyAdmin:

SELECT * FROM `drupal_corpus_text` WHERE replace(text, "'", "") LIKE "%sinka%"


It perfectly finds the record containing sin'ka.

But I cannot figure how to do it using Drupal features.

Here is what I try to do:

  $apostrophe = "'";
  $query = db_select('corpus_text', 'n')
    -&gt;fields('n', array('text_id', 'speaker', 'text'))
    -&gt;condition('n.text', '%' . db_like($key_search) . '%', 'LIKE')
    -&gt;orderBy('speaker', 'ASC');
  $query-&gt;addExpression('REPLACE(n.text, "'.$apostrophe.'", "")');
  $query-&gt;execute();


And here is the error I get:

PDOException: SQLSTATE[42S22]: 
Column not found: 1054 Unknown column ''' in 'field list': 
SELECT n.text_id AS text_id, n.speaker AS speaker, n.text AS text, 
REPLACE(n.text, "'", "") AS expression FROM {corpus_text} n 
WHERE (n.text LIKE :db_condition_placeholder_0 ESCAPE '\\') ORDER BY speaker ASC


I cannot figure out how I must properly use all the quotes and the apostrophe mark to make it work.

The symbol used in those texts is U+0027.
There are a couple of points here:


Your Drupal query is wrong insofar as it wouldn't produce your example SQL. You don't need to add an expression, rather a where clause
Quotes are fun in MySQL (see SQL modes). Your default mode would appear to be ANSI_QUOTES, which means:


  Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character.


Which explains why MySQL is trying to interpret the single quote as a column name. PMA probably sets a different mode per-query, so the same SQL works in that environment.


Taking those into account, this is more like what you're hoping for:

$query = db_select('corpus_text', 'n')
  -&gt;fields('n', array('text_id', 'speaker', 'text'))
  -&gt;where("REPLACE(n.text, '''', '') LIKE :search", array(':search' =&gt; '%' . db_like($key_search) . '%'))
  -&gt;orderBy('speaker', 'ASC');


Which produces:

SELECT  n.text_id AS text_id, n.speaker AS speaker, n.text AS text
FROM 
{corpus_text} n
WHERE  (REPLACE(n.text, '''', '') LIKE :search) 
ORDER BY speaker ASC


Notice that the single quote is escaped with two single quotes, MySQL's way of handling this situation.