SQL LIKE仅匹配完整名称问题排查及解决求助

Let's break down what's going wrong here and fix it!

The Core Problem #

Your LIKE condition is backwards. Right now, your code checks if the input parameter (@ItemDesc) contains the item description stored in the table (a.itemdescription):

AND (@ItemDesc like '%'+ a.itemdescription +'%')

That's why full matches work: when you pass 'Cow Dung', the parameter exactly equals the table's value, so '%Cow Dung%' matches 'Cow Dung'. But when you pass a partial value like 'Cow', you're asking if 'Cow' contains the longer description from the table (e.g., 'Cow Dung - ')—which it doesn't, hence no results.

The Fix #

Flip the LIKE condition to check if the table's item description contains the input parameter. This way, any partial match (even a single character) will return rows where the description includes your input:

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

Bonus: Handle Empty/Null Parameters #

If there's a chance @ItemDesc might be null or left blank, add a check to avoid filtering out all rows when no value is provided:

AND (@ItemDesc IS NULL OR @ItemDesc = '' OR a.itemdescription like '%' + @ItemDesc + '%')

This ensures the query returns all active items when no parameter is passed, and matches any relevant rows when a partial (or full) value is input—even single characters like 'C' or 'w'.

内容的提问来源于stack exchange，提问作者user8507737