Using "like" wildcard in prepared statement
Code it like this:
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM analysis WHERE notes like ?");
pstmt.setString(1, notes + "%");`
Make sure that you DO NOT include the quotes ' ' like below as they will cause an exception.
pstmt.setString(1,"'%"+ notes + "%'");
PreparedStatement ps = cn.prepareStatement("Select * from Users where User_FirstName LIKE ?");
ps.setString(1, name + '%');
Try this out.
You need to set it in the value itself, not in the prepared statement SQL string.
So, this should do for a prefix-match:
notes = notes
.replace("!", "!!")
.replace("%", "!%")
.replace("_", "!_")
.replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");
or a suffix-match:
pstmt.setString(1, "%" + notes);
or a global match:
pstmt.setString(1, "%" + notes + "%");
We can use the CONCAT
SQL function.
PreparedStatement pstmt = con.prepareStatement(
"SELECT * FROM analysis WHERE notes like CONCAT( '%',?,'%')";
pstmt.setString(1, notes);
ResultSet rs = pstmt.executeQuery();
This works perfectly for my case.