CODEWITHSUNDEEP
sqlsql-like
user8507737
user8507737

Reputation:

SQL LIKE is only working With Full Name

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?

Upvotes: 1

Views: 148

Answers (2)

Vishnu Chandel
Vishnu Chandel

Reputation: 133

Comparison variable should be always at the right side of the statement. Your new query should be as below. 

select distinct (a.itemid), a.itemcode, v.itemdescription    
from aitem a
    INNER JOIN vwitemdescription v ON a.itemID = v.itemID     
WHERE a.active=1    
--changed condition start    
AND (a.itemdescription like '%'+ @ItemDesc +'%')
--changed condition end

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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 + '%';

Upvotes: 4

Related Questions