Solved

SQL filter on dynamic keyword

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Total in Access 4 47
SQL Server 2012 rs - Sum each category by month 4 32
How do i get a breakdown of totals by age range? 10 22
sql server insert 12 30
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard 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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

805 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