Solved

SQL filter on dynamic keyword

Posted on 2014-10-10
5
134 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
Comment Utility
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:
ScottPletcher earned 500 total points
Comment Utility
WHERE
    (@FILTER = '' OR DESCRIPTION LIKE REPLICATE('$', LEN(@FILTER)) + '[^$]%')
0
 
LVL 14

Expert Comment

by:ThomasMcA2
Comment Utility
@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:ScottPletcher
Comment Utility
Nah, check it again.  You got my initial stab it only :-) .
0
 

Author Closing Comment

by:holemania
Comment Utility
Thank you.  This works wonderfully and just what I need.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now