SQL fuzzy search

Question

I have to call SQL database search in my Java program.

The retrieving condition is that user input string partially matches NAME filed and not case sensitive. For example, if user input "joe", students' records with name like "Joea", "Bjoe","JOEED" should be returned.

While I tried to write code as bellow. It doesn't seem to be able to work out.

Can someone tell me why? Thanks.

String fuzzySearch = "UPPER(%" + inputStr + "%)";
String query = "SELECT * FROM student WHERE UPPER(student.name) LIKE ? ";
PreparedStatement prepStatement = con.prepareStatement(query.toString());
prepStatement.setObject(1,fuzzySearch);

What on earth does "It doesn't seem to be able to work out." mean? It throws an error? If so, what's the error? It doesn't return the expected results? If not, what *is* it returning? — Anthony Grist, Jun 30 '14 at 10:39
http://gordonlesti.com/fuzzy-fulltext-search-with-mysql/ Even though it is php, you can easily translate that into java. The important part was that it uses mysql which seems to be important in your case. — Pphoenix, Jun 30 '14 at 10:41

score 3 · Accepted Answer · answered Jun 30 '14 at 10:42

As you are using a string bind variable, you are saying

WHERE UPPER(student.name) LIKE 'UPPER(%joe%)'

instead of

WHERE UPPER(student.name) LIKE UPPER('%joe%')

So use

String fuzzySearch = "%" + inputStr + "%";
String query = "SELECT * FROM student WHERE UPPER(student.name) LIKE UPPER(?) ";
PreparedStatement prepStatement = con.prepareStatement(query.toString());
prepStatement.setObject(1,fuzzySearch);

instead.

SQL fuzzy search

1 Answers1