USING THE SQL LIKE OPERATOR WITH %%

Question

I had a requirement to create a query in MS SQL where the search condition would include/exclude a table based on user input.

Say I have two tables’ TABLE_A and TABLE_B with columns KEYCOLUMN_A, COLUMN_A in TABLE_A and columns FKCOLUMN_B, COLUMN_B in TABLE_B

And a query like

SELECT TABLE_A.* FROM TABLE_A, TABLE_B WHERE TABLE_A.KEYCOLUMN_A = TABLE_B.FKCOLUMN_B
AND TABLE_A.COLUMN_A LIKE '%SEARCH%' AND TABLE_B.COLUMN_B LIKE '%SEARCH2%'

Now if user does not input SEARCH2, I don’t need to search TABLE_B. But this would mean an IF ELSE clause. And as the number of ‘optional’ tables in the query increases, the permutations and combinations would also increase and there will be many IF and ELSE statements.

Instead I decided to keep the statement as it is. So if SEARCH2 is empty, the query will effectively become

SELECT * FROM TABLE_A, TABLE_B WHERE TABLE_A.KEYCOLUMN_A = TABLE_B.FKCOLUMN_B
AND TABLE_A.COLUMN_A LIKE '%SEARCH%' AND TABLE_B.COLUMN_B LIKE '% %'

I wonder whether SQL optimizer can recognize that LIKE %% is as good as removing the condition itself

Answer 1

how about wrapping and OR around your "B" table, such as

AND (len(searchString)=0 or table_b.column_b like "%searchString%" )

This way, if no value for the string, its length would be zero, and the first part of the OR would be evaluated, always come back as true and return that portion of the equation as valid and ignore the other half using the LIKE clause.

You could apply the same for as many linked tables as you need.

Answer 2

In mySql you can also use ILIKE then it's case incensitive

Answer 3

you don't say which database you are using, here is the most extensive article (SQL Server) I've ever seen on the subject:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

Answer 4

First thing, you have a space in your example:

AND TABLE_B.COLUMN_B LIKE '% %'

That will never be optimized as is indeed a significant condition.

Now, I think that if it is optimized away depends on the database engine and how smart it is.

For example, SQL Server 2005 does offer the same execution plan for the two types of queries, while MySQL 5.0.38 does not.

Answer 5

you can rewrite you query like this:

SELECT TABLE_A.* FROM TABLE_A, TABLE_B WHERE TABLE_A.KEYCOLUMN_A = TABLE_B.FKCOLUMN_B 
AND (@paramA='' or TABLE_A.COLUMN_A LIKE '%' + @paramA + '%') 
AND (@paramB='' or TABLE_B.COLUMN_B LIKE '%' + @paramB + '%')

this way if paramA or paramB is '' then the other column that is queried inside same paranthesys will not be queried.

Answer 6

Use UNION and proper JOINs?

The %foo% searcb term is bad enough (can't use index) without adding OR and LEN to the mix too.

SELECT 
    TABLE_A.*
FROM
    TABLE_A
    JOIN
    TABLE_B On TABLE_A.KEYCOLUMN_A = TABLE_B.FKCOLUMN_B
WHERE
    TABLE_A.COLUMN_A LIKE '%SEARCH%' AND TABLE_B.COLUMN_B LIKE '%SEARCH2%'
UNION
SELECT 
    TABLE_A.*
FROM
    TABLE_A
WHERE
    TABLE_A.COLUMN_A LIKE '%SEARCH%'