How do I use the LIKE wildcard in prepared statements for keyword-based searches in MySQL?

Using "like" Wildcard in Prepared Statement

When implementing search functionality based on a keyword using prepared statements in SQL, it's necessary to utilize the LIKE keyword. However, understanding how to incorporate it into the prepared statement can be confusing.

To specify the keyword text in the prepared statement, it's crucial to set it within the value itself, not in the SQL string of the prepared statement. As such, the proper way to implement a prefix-match LIKE search would be as follows:

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

Alternatively, suffix-match and global match variants can be implemented using similar techniques:

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

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

By using these techniques, one can effectively utilize the LIKE keyword within prepared statements to perform keyword-based searches in MySQL queries.

The above is the detailed content of How do I use the LIKE wildcard in prepared statements for keyword-based searches in MySQL?. For more information, please follow other related articles on the PHP Chinese website!