How to Use the LIKE Wildcard with Prepared Statements in MySQL?

Using the "like" wildcard with prepared statements

When using prepared statements to execute MySQL database queries with a search functionality based on a keyword, the "like" wildcard can be used to find partial matches. To use the "like" wildcard with a prepared statement, it must be set in the value itself, rather than in the prepared statement SQL string.

For a prefix match, the value should be replaced with the keyword followed by a percent sign ("%"), while escaping any special characters in the value. For example:

notes = notes
    .replace("!", "!!")
    .replace("%", "!%")
    .replace("_", "!_")
    .replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
        "SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");

For a suffix match, the value should be set with a percent sign followed by the keyword.

pstmt.setString(1, "%" + notes);

For a global match, the value should be set with a percent sign before and after the keyword.

pstmt.setString(1, "%" + notes + "%");

The above is the detailed content of How to Use the LIKE Wildcard with Prepared Statements in MySQL?. For more information, please follow other related articles on the PHP Chinese website!