wpdb query not returning results containing single quotes

Here is an example of performing a LIKE based query while escpaing the value parameter passed to the query:

$results = $wpdb->get_results( 
    $wpdb->prepare( 
        "SELECT post_title, ID from $wpdb->posts WHERE post_title LIKE '%%%s%%'", 
        $wpdb->esc_like("today's weather is nice")
    ) 
);

Update:

Based on your edit, your query is incorrectly formed hence the error, it should be:

global $wpdb;

$itemName = get_the_title($ID);

$results = $wpdb->get_results(
    $wpdb->prepare(
        "
        SELECT ID 
        FROM $wpdb->posts
        WHERE $wpdb->posts.post_title 
        LIKE '%%%s%%' 
        AND $wpdb->posts.post_status="publish" 
        ORDER BY post_title ASC
        ", 
        $wpdb->esc_like($itemName)
    )
);