Can't search MS Access database using LIKE with % or *

Question

I am trying to query a MS Access database from my java code and I am having no luck with the % wildcard that I read should work in other posts. The LIKE operator works if I exclude the wild cards from my code and provide the searchText variable with a value that matches exactly to a records Description. Below is the String queries I have tried that have either returned null or an exception:

String tableName = "SSPWO"; String desc = "[Description]";

  String query = String.format("SELECT * FROM %s WHERE " + desc + " LIKE '%" searchText +  "%' ORDER BY [WorkOrderNo] DESC", tableName);

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

String tableName = "SSPWO"; String desc = "[Description]";

  String query = String.format("SELECT * FROM %s WHERE " + desc + " LIKE '*" searchText +  "*' ORDER BY [WorkOrderNo] DESC", tableName);

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

String tableName = "SSPWO"; String desc = "[Description]";

  String query = String.format("SELECT * FROM %s WHERE " + desc + " LIKE \"%" searchText +  "%\" ORDER BY [WorkOrderNo] DESC", tableName);

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

String tableName = "SSPWO"; String desc = "[Description]";

  String query = String.format("SELECT * FROM %s WHERE " + desc + " LIKE \'%" searchText +  "%\' ORDER BY [WorkOrderNo] DESC", tableName);

Any help is greatly appreciated.

Don't just say "null or an exception": Post the stack traces for each of the failing cases. (Also, you should use prepared statements instead of string pasting to construct queries.) — chrylis -cautiouslyoptimistic-, Feb 20 '14 at 20:37

score 1 · Answer 1 · answered Feb 20 '14 at 20:38

1

You've got a whack of syntax errors anyways:

String query = String.format([..snip...] + " LIKE '%" searchText 
                                                     ^---missing +

answered Feb 20 '14 at 20:38

Marc B

356,200
43
426
500

score 1 · Accepted Answer · answered Feb 20 '14 at 22:00

The percent sign (%) is definitely the right wildcard character to use in this case. For test data

ID  Description                        
--  -----------------------------------
 1  I like apple pie.                  
 2  This is a test.                    
 3  Apple makes iThings.               
 4  This is another test.              
 5  Name of a tropical fruit: pineapple

the code

String tableName = "SSPWO"; 
String desc = "[Description]";
String searchText = "apple";
PreparedStatement ps = con.prepareStatement(
        String.format("SELECT * FROM %s WHERE %s LIKE ?", tableName, desc)
        );
ps.setString(1, "%" + searchText + "%");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    System.out.println(String.format("%d: %s", rs.getInt(1),rs.getString(2)));
}

produces the following console output

1: I like apple pie.
3: Apple makes iThings.
5: Name of a tropical fruit: pineapple

Thank you. This worked. The prepared statement was definitely the way to go. — user3334496, Feb 26 '14 at 22:43

Can't search MS Access database using LIKE with % or *

2 Answers2