Running PreparedStatement with Like clause with wildcard

Question

I'm trying to execute following SQL query where it tries to find results that matches the column2 values ending with abc

 PreparedStatement stmt = conn.prepareStatement("SELECT column1 FROM dbo.table1 WHERE column2 LIKE ?");
 stmt.setString(1, "%" +"abc");

But it returns nothing even though there is a matching value. This only happens with SQL Server. Same query with informix database returns correct results. Anyone has an idea about what causing this to behave differently?

Is this due to an issue in how PreparedStatement creates the SQL query for SQL Server?

Edit

I found out this happens when the data in the column which i perform the like contain space. eg: when the column contains "some word" and if i perform the search by stmt.setString(1, "%" + "word"); it won't return a matching result but if i perform the same on for "someword" it would return the matching result

Answer 1

SQL Server accepts wild characters in the LIKE clause within the single quotation marks, like this ''.

A sample SQL query:

SELECT NAME FROM VERSIONS WHERE NAME LIKE 'Upd%'

The query above will yield you results on SQL Server. Applying the same logic to your Java code will retrieve results from your PreparedStatement as well.

PreparedStatement stmt = conn.prepareStatement("SELECT NAME FROM VERSIONS WHERE NAME LIKE ?");
stmt.setString(1, "Upd%");

I've tested this code on SQL Server 2012 and it works for me. You need to ensure that there are no trailing spaces in the search literal that you pass on to your JDBC code.

Though as a side note, you need to understand that a wildcard % used in the beginning, enforces a full table scan on the table which can deteriorate your query performance. A good article for your future reference.

Hope this helps!