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

Using LIKE Wildcard with Prepared Statements in MySQL Queries

In MySQL database queries, the LIKE operator allows for pattern-based searches. When using prepared statements to execute such queries, it is essential to correctly incorporate the LIKE wildcard.

One common requirement is to perform a prefix-match search. To achieve this, the keyword% should be appended to the search term. However, when using prepared statements, it is incorrect to concatenate the wildcard directly to the parameter value.

Instead, set the value itself within the prepared statement query. For a prefix-match, modify the search term as follows:

notes = notes
    .replace("!", "!!")
    .replace("%", "!%")
    .replace("_", "!_")
    .replace("[", "![");

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

The ESCAPE keyword indicates the escape character used to escape special characters within the wildcard. By default, it is a backslash (), but it can be changed to any character.

Similarly, for a suffix-match, use the following:

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

And for a global match:

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

By setting the wildcard in the search term, the LIKE operator can be used effectively with prepared statements to perform various pattern-based searches in MySQL databases.

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