Support » Plugins » Hacks » How to escape % in LIKE query clause

  • I want to do a query to find something like 75% in a text field.
    I googled and found 2 implementations, both do not seem to work:

    SELECT ... WHERE myCol LIKE '%75[%]%'

    and

    SELECT ... WHERE myCol LIKE '%75!%%' ESCAPE '!'

    Can i do such a thing in wp?

Viewing 5 replies - 1 through 5 (of 5 total)
  • I can’t tell you exactly 75%, but in my searchform i use:

    SELECT ... WHERE myCol LIKE '%$searchtitel%'

    Just replace $seatchtitel with your string, and put it in % %

    If you search for “Hello” you will find all entries including hello like “Hello World”.

    Moderator bcworkz

    (@bcworkz)

    Maybe it depends on context, but both of these worked for me in WP on a custom page template:

    $x = $wpdb->get_results("SELECT *
       FROM wp_postmeta
       WHERE meta_value LIKE '%75!%%'
       ESCAPE '!'");
    $x = $wpdb->get_results("SELECT *
       FROM wp_postmeta
       WHERE meta_value LIKE '%75\\%%'");

    Thread Starter Jacob N. Breetvelt

    (@opajaap)

    Thank you for your replies.

    Actually i am looking for the text %%wppa%%.

    The problem is that all methods, including:

    $has_wppa_scripts = $wpdb->get_results( "SELECT ID, post_title, post_content, post_type " .
    					"FROM " . $wpdb->prefix . 'posts' ." " .
    					"WHERE post_status = 'publish' " .
    					"AND ( post_type = 'post' OR post_type = 'page' ) " .
    					"AND post_content LIKE '%\\%\\%wppa\\%\\%%' " , ARRAY_A );

    (backticks left out)
    also find %<b></b>%wppa%%.

    I can filter them out later, but i would prefer a method that works straight away.

    ( %%wppa%% works like a shortcode, i use %<b></b>%wppa%% for documentation purposes where it may not be recognized as ‘shortcode’, and i want to test on real %%wppa%% for an admin notice as they have to convert this to the real shortcode [wppa]. see: http://wppa.nl/changelog/script-to-shortcode-conversion/ )

    Moderator bcworkz

    (@bcworkz)

    Hmmm, I guess LIKE does not “see” HTML tags. That’s rather unexpected, but makes sense in a way. Could you use something besides HTML tags to make your documentation version, maybe by inserting a non-printing character, or maybe by using thin space if some sort of printing character is required? Thin space shouldn’t be too obtrusive, assuming the display font actually has a thin space character.

    You might also try using an HTML entity for the ‘%’ characters, or the \x hexcode equivalent, i.e. &# 37; or \x 25 (without spaces)

    Thread Starter Jacob N. Breetvelt

    (@opajaap)

    That would require that i change the pages where the ‘invalidated’ %%wppa%% tokens exist. Then it is simpler to filter the ‘false positives’ out like this:

    if ( $has_wppa_scripts ) {
    	foreach( array_keys( $has_wppa_scripts ) as $key ) {
    		if ( strpos( $has_wppa_scripts[$key]['post_content'], '%%wppa%%' ) === false ) {
    			unset( $has_wppa_scripts[$key] );
    		}
    	}
    }

    This is what i will do. Good to know anyway that using LIKE has its limitations. If someone wants to report thsi as a bug in MySql, go ahaed, I will use the post-query filtering; this works. #pragma
    Topic closed
    Thanx for your feedback.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘How to escape % in LIKE query clause’ is closed to new replies.