Solved

SQL filter on dynamic keyword

Posted on 2014-10-10
5
150 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
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 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