Solved

SQL filter on dynamic keyword

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax issue with my Where Clause SQL 2012 20 42
denied execute as 13 57
T-SQL: Only Wanting One Record 8 60
Creating a View from a CTE 15 46
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…

734 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