Home >Java >javaTutorial >How to Use LIKE Wildcard with Prepared Statements in MySQL Queries?

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

Patricia Arquette
Patricia ArquetteOriginal
2024-11-15 09:11:03872browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn