Wildcard `LIKE` support for `WP_Meta_Query` (and maybe other queries?)

[boonebgorges]

Passing LIKE as the compare parameter for a meta query (or compare_key, after [42768]) results in what is essentially a "contains" query for the literal value (or key) string. So:

array(
    'compare' => 'LIKE',
    'value' => 'foo',
)

becomes WHERE ... value LIKE '%foo%'.

MySQL LIKE wildcard characters % and _ are escaped, so it's not possible to do a non-standard anchored search, or any other custom LIKE query.

It would be nice if you could pass a string like foo% and have the wildcard characters respected.

A few considerations:

1. Syntax. A potential solution is suggested in https://core.trac.wordpress.org/ticket/42409#comment:14. Another possibility is a filter for enabling the "non-escaped" version
2. Security. I'm unsure that there's a way to do this without introducing security issues, specifically around the % character. Any potential patch would need serious review.

[boonebgorges]

Closely related: #23373