SQL LIKE is only working With Full Name

Question

In one of my sp I have following lines of code

select distinct (a.itemid), a.itemcode, v.itemdescription

from aitem a
    INNER JOIN vwitemdescription v ON a.itemID = v.itemID 

WHERE a.active=1
-----------------------------------------
AND (@ItemDesc like '%'+ a.itemdescription +'%')
-----------------------------------------

If I give @ItemDesc value in full description, I get values and if I give @ItemDesc value in half description I get nothing in return.

For Example :

If I giv

@ItemDesc = 'Cow Dung - '

I get result as

---------------------------------------
|itemid | itemcode | itemdescription  |
--------------------------------------
|   63  | 40-17005 |   Cow Dung -     |
---------------------------------------

And even if I cut the string as @ItemDesc = 'Cow Dung' I get the same results,

But if I cut it into @ItemDesc = 'Cow' or only to single character I don't get any results.

I want to load the item even if I enter only a single charecter in it.

Is there anything wrong with my code? How to get it right?

Answer 1

You need to switch the items in your LIKE expression:

AND a.itemdescription LIKE '%' + @ItemDesc + '%'

Using this logic, any substring of the itemdescription would match. For example the following is a true condition:

AND `Cow Dung - ` LIKE '%Cow%'

Here is the full query:

SELECT DISTINCT
    a.itemid, a.itemcode, v.itemdescription
FROM aitem a
INNER JOIN vwitemdescription v
    ON a.itemID = v.itemID 
WHERE
    a.active = 1 AND
    a.itemdescription LIKE '%' + @ItemDesc + '%';