Using the SQL LIKE operator with %%

Question

I had a requirement to create a query in SQL Server 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 and COLUMN_A in TABLE_A and columns FKCOLUMN_B and 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 the 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 '% %'

Can the SQL optimizer recognize that LIKE %% is as good as removing the condition itself?

There is a typo in the second code sample. There should not be any space between % and %. SELECT * FROM TABLE_A, TABLE_B WHERE TABLE_A.KEYCOLUMN_A = TABLE_B.FKCOLUMN_BAND TABLE_A.COLUMN_A LIKE '%SEARCH%' AND TABLE_B.COLUMN_B LIKE '%%' — devanalyst, Sep 25 '09 at 11:49

score 9 · Accepted Answer · edited May 22 '16 at 16:16

9

Wrap an 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.

edited May 22 '16 at 16:16

Peter Mortensen

30,738
21
105
131

answered Sep 25 '09 at 11:24

DRapp

47,638
12
72
142

***searchString*** is `''` ? – Kiquenet Oct 25 '18 at 09:22

score 2 · Answer 2 · edited May 22 '16 at 16:27

LIKE is used with the WHERE clause to search, update, and delete a record using wild cards.

Example:

To search all records whose employee name is starred from a character, 'a':

select * from Employee where Name like 'a%'

To update all records with name amit whose employee name is starting with a character, 'a':

update Employee set Name='amit' where Name like 'a%'

To delete all records whose employee name is starting with a character, 'a':

delete from Employee  where Name like 'a%'

score 2 · Answer 3 · edited May 22 '16 at 16:22

2

First thing, you have a space in your example:

AND TABLE_B.COLUMN_B LIKE '% %'

That will never be optimized as it 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.

edited May 22 '16 at 16:22

Peter Mortensen

30,738
21
105
131

answered Sep 25 '09 at 11:31

Vinko Vrsalovic

330,807
53
334
373

There is a typo in the second code sample. There should not be any space between % and %. SELECT * FROM TABLE_A, TABLE_B WHERE TABLE_A.KEYCOLUMN_A = TABLE_B.FKCOLUMN_BAND TABLE_A.COLUMN_A LIKE '%SEARCH%' AND TABLE_B.COLUMN_B LIKE '%% – devanalyst Sep 25 '09 at 11:51

score 0 · Answer 4 · edited May 22 '16 at 16:17

0

In MySQL you can also use ILIKE, and then it's case insensitive.

edited May 22 '16 at 16:17

Peter Mortensen

30,738
21
105
131

answered Sep 25 '09 at 11:27

Elitmiar

35,072
73
180
229

How does this answer the question? – Peter Mortensen May 22 '16 at 16:17

score 0 · Answer 5 · edited May 22 '16 at 16:24

0

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 parentheses will not be queried.

edited May 22 '16 at 16:24

Peter Mortensen

30,738
21
105
131

answered Sep 25 '09 at 12:00

Numenor

1,686
12
20

I think you mean the opposite. I checked the above code and it works when the condition is @paramA = '' which in effect is what DRapp's code is doing – devanalyst Sep 25 '09 at 14:11

score 0 · Answer 6 · edited May 22 '16 at 16:25

0

Use UNION and proper JOINs.

The %foo% search 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%'

edited May 22 '16 at 16:25

Peter Mortensen

30,738
21
105
131

answered Sep 25 '09 at 15:28

gbn

422,506
82
585
676

score 0 · Answer 7 · answered Feb 18 '15 at 03:43

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column

LIKE '%[p-s]'  -- "It search data from table parameter where sentence ending with p,q,r,s word."
LIKE '[0-9]' --It use for search only numeric value
LIKE '%table%' -- it use for search parameter from table where use "table" keyword'.
LIKE %[^p-r]  -- it set the condition where Not Ending With a Range of Characters

Example: 
SELECT T1.BrandName,T1.BrandID,T2.CategoryName,T2.Color FROM TABLE1 T1 
        LEFT JOIN TABLE2 T2 on T1.ID  = T2.BrandID
        WHERE T1.BrandName LIKE '%Samsung%'
Example: 
SELECT T1.BrandName,T1.BrandID,T2.CategoryName,T2.Color FROM TABLE1 T1 
        LEFT JOIN TABLE2 T2 on T1.ID  = T2.BrandID
        WHERE T1.BrandName LIKE '%[a-j]'

Using the SQL LIKE operator with %%

7 Answers7

Linked