holemania
asked on
SQL filter on dynamic keyword
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
FROM PART_MASTER
WHERE (
(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?
SELECT ITEM_ID, DESCRIPTION
FROM PART_MASTER
WHERE (
(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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Scott, wouldn't your idea select $$$ entries when $$ is entered? (Since the left 2 columns of $$$ match the input of $$.)
Nah, check it again. You got my initial stab it only :-) .
ASKER
Thank you. This works wonderfully and just what I need.
To fix your issue, add a check for the next character using SUBSTR, like this:
Open in new window
Also, you only need to check if @FILTER = '' once.
The above ideas create this solution:
SELECT ITEM_ID, DESCRIPTION
FROM PART_MASTER
WHERE (
@FILTER = ''
OR (LEFT(DESCRIPTION, 1) = @FILTER AND SUBSTR(DESCRIPTION, 2, 1) <> "$")
OR (LEFT(DESCRIPTION, 2) = @FILTER AND SUBSTR(DESCRIPTION, 3, 1) <> "$")
OR (LEFT(DESCRIPTION, 3) = @FILTER
)