How to Use Wildcards in Prepared Statements with LIKE?

Wildcard Queries in Prepared Statements with LIKE

When using prepared statements for database queries, implementing a search functionality with keywords often requires the use of the LIKE operator. This guide provides a comprehensive solution on how to achieve this with prepared statements.

To utilize the LIKE operator with prepared statements, you can append the wildcard symbol (%) to the search term within the value provided to the prepared statement, such as:

String notes = "keyword%";
PreparedStatement pstmt = con.prepareStatement(
      "SELECT * FROM analysis WHERE notes LIKE ?");
pstmt.setString(1, notes);
ResultSet rs = pstmt.executeQuery();

By setting the value with the appended wildcard, you enable a query that matches all records where the "notes" column contains the input keyword as a substring.

However, certain characters have special meanings in SQL, including %, !, [, _, and ]. To ensure proper handling of these characters, they should be escaped using the ESCAPE clause in the prepared statement. For instance:

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

By replacing these characters with their escaped versions, the prepared statement will correctly interpret the wildcard and match records accordingly.

Depending on your search requirements, you can adjust the placement of the wildcard to achieve different matching scenarios:

• Prefix match: "%" appended to the end of the keyword (e.g., "keyword%")
• Suffix match: "%" prepended to the beginning of the keyword (e.g., "%" "keyword")
• Global match: "%" appended to both ends of the keyword (e.g., "%" "keyword" "%")

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