Solved

SQL filter on dynamic keyword

Posted on 2014-10-10
5
165 Views
Last Modified: 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
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?
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40373559
The "<>" in your code should be "=".

To fix your issue, add a check for the next character using SUBSTR, like this:
SUBSTR(DESCRIPTION, 2, 1) <> "$"

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
)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40373561
WHERE
    (@FILTER = '' OR DESCRIPTION LIKE REPLICATE('$', LEN(@FILTER)) + '[^$]%')
0
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40373571
@Scott, wouldn't your idea select $$$ entries when $$ is entered? (Since the left 2 columns of $$$ match the input of $$.)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40373585
Nah, check it again.  You got my initial stab it only :-) .
0
 

Author Closing Comment

by:holemania
ID: 40373689
Thank you.  This works wonderfully and just what I need.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question