WordPress SQL LIKE request doesn’t work for fields with special symbols

What I miss here? How to search through any elements in this field?

Your SQL statements, or the LIKE '%a%' and LIKE '%full%', are good.

But the wpdb::prepare()‘s documentation says, “Literal percentage signs (%) in the query string must be written as %%“, hence you should actually use LIKE '%%a%%' and LIKE '%%full%%'.

But then the documentation also says that:

Percentage wildcards (for example, to use in LIKE syntax) must be
passed via a substitution argument containing the complete LIKE
string, these cannot be inserted directly in the query string. Also
see
wpdb::esc_like().

Therefore with wpdb::prepare(), you would want to do it like so which is based on the example in the esc_like()‘s documentation:

_{Additionally, instead of hard-coding the table name (wp_postmeta), I used $wpdb->postmeta — it’s a core property and is equivalent to {$wpdb->prefix}postmeta used in a double-quoted string "like this".}

$wild = '%';
$find = 'full';
$like = $wild . $wpdb->esc_like( $find ) . $wild; // build the complete LIKE string

// I wrapped the statement for brevity.
$query = $wpdb->prepare( "
    SELECT meta_value
    FROM $wpdb->postmeta
    WHERE meta_key = 'job_bm_job_type'
        AND meta_value LIKE %s
    LIMIT 1
", $like );

Why exactly %a% worked, but %full% did not work

• %full% starts with %f which is one of the supported placeholders for wpdb::prepare() (the other two placeholders are %s and %d).

  And because the prepare() method detected a placeholder in the query and yet you didn’t specify the 2nd parameter for that method, then it returned an empty string and thus no query was made.

  But yes, that %f was mistakenly seen as a placeholder, which is a reason why %% should be used for literal percentage signs in the query.

• %a% worked because it does not start with a supported placeholder and thus the prepare() method treated the % as literal percentage signs. (But MySQL treated them as wildcards)

So in response to your answer, adding the \" worked because it changed the %f to %\"f (or just %"f when passed to MySQL), which makes it no longer a placeholder.

But then I advise you to use the “complete LIKE string” approach as you could see in my example above.