SQL filter on dynamic keyword
Posted on 2014-10-10
I have a table with a "Description" field. In the description field we either put a no dollar sign, 1 dollar sign, 2 dollar sign, and 3 dollar. Each dollar sign symbolize something. I want to create a filter, where the user can put in no dollar sign and it will pull everything. Filter with just 1 dollar sign and only those with 1 dollar sign will get filter out etc.
SELECT ITEM_ID, DESCRIPTION
(LEFT(DESCRIPTION, 1) <> @FILTER OR @FILTER = '')
AND (LEFT(DESCRIPTION, 2) = @FILTER OR @FILTER = '')
AND (LEFT(DESCRIPTION, 3) = @FILTER OR @FILTER = '')
Only issue with the above is that if I put in a $, it filter everything including the rows with the 2 and 3 dollar sign as will. Any ideas?