Wildcards and Like Filters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Wildcards and Like Filters

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-14 : 15:20:45

I need to do Multiple Wildcard Filters, according a selector on a form.
If I was to Hardcode the first one It would look Like This.

Select Select dbo.Sub_die .*
From dbo.Sub_die
WHERE (dbo.Sub_die.C_Style LIKE '%SF%')

But since I need to do a variety of filters I came up with this.(note I only added 3 for testing)

Create Procedure dbo.testfilter
(@Styleselect Int)
Declare @Style Varchar

Select @Style = Case When @Styleselect = 0
Then ‘%SF%’

When @Styleselect = 1
Then ‘%RTE%’

When @Styleselect = 2
Then ‘%POUCH%’
End

Select dbo.Sub_die .*
From dbo.Sub_die
WHERE (dbo.Sub_die.C_Style LIKE @Style)

Unfortunately it seems by adding a local variable, the Wildcard Filter and Like Statement gets totally lost.

Any Ideas?

Jim

Edited by - JimL on 04/14/2003 15:31:58

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-14 : 15:40:34

Make sure your @Style variable is being set correctly; you are declaring it as a vachar() with no length; does that work? Also, try just printing the variable to determine it has the value you expect, instead of running the SQL statement.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 15:40:59

Why not use dynamic SQL, since a leading '%' is going to cause very poor performance anyway (oh and I would not reccommend Select *, except in development, and why do people use parenthesis around predicates to isolate them?)

I think the follwoing should work though.


Create Procedure testfilter @Styleselect Int 
AS
Declare @SQL varchar(1000), @Style Varchar(20)

Select @SQL = 'Select Select dbo.Sub_die .*' 
+ ' From Sub_die' 
+ ' WHERE C_Style LIKE ' 
+  Case When @Styleselect = 0 Then '%SF%'
	When @Styleselect = 1 Then '%RTE%'
	When @Styleselect = 2 Then '%POUCH%'
			      ELSE '%' 
   End
Exec(@SQL)
GO

However I might reccommend the following (I just wish didn't have to require the leading '%' though):


Create Procedure testfilter @Styleselect Int 
AS
When @StyleSelect Not In (0, 1, 2)
	BEGIN
--		Error handling message
		Return
	END

When @StyleSelect = 0
	BEGIN
		Select Select dbo.Sub_die .* From Sub_die WHERE C_Style LIKE '%SF%'
	END
When @StyleSelect = 1
	BEGIN
		Select Select dbo.Sub_die .* From Sub_die WHERE C_Style LIKE '%RTE%'
	END
When @StyleSelect = 2
	BEGIN
		Select Select dbo.Sub_die .* From Sub_die WHERE C_Style LIKE '%POUCH%'
	END
GO

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 15:46:04

quote:

(oh and I would not reccommend Select *, except in development, and why do people use parenthesis around predicates to isolate them?)

I agree with SELECT * for sure. I use parenthesis in my WHERE statements when I have multiple parts to it, such as:

SELECT Column1, Column2
FROM Table1
WHERE ((Column1 IS NOT NULL) AND (Column4 BETWEEN 1 AND 100)) OR (Column5 = 0)

This is a stupid example, but I think that it shows why I use it sometimes. But I don't always use them:

SELECT Column1, Column2
FROM Table1
WHERE Column2 IS NOT NULL AND Column3 = 0

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 15:46:59

Jeff:

There's quite a couple things going wrog here. I cut and pasted the code, and the wrong quites (as far as I can tell) are being used here. Can you identify what the quote chars are suppose to be? Abd yes you can use varchar by itself. It just give you 1 char back though, so whats the point.

Anyway this modified code works:

Declare @Style Varchar(10), @styleselect int

Select @styleselect = 0

Select @Style = Case When @Styleselect = 0
Then '%SF%'
When @Styleselect = 1
Then '%RTE%'
When @Styleselect = 2
Then '%POUCH%'
End

Select @Style

Change varchar back to no length def to see the truncation

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 15:53:25

Tara,

yeah. In your example they're required (well maybe only the two).

quote:

WHERE (Column1 IS NOT NULL AND Column4 BETWEEN 1 AND 100) OR Column5 = 0

But is it an access thingy that everyone just adopts. Sometime I think that the benefits of access are outweighed by the amount of damage it does (bad designs of the front and back ends). That we get stuck maintaining.

Brett

8-)

Edited by - x002548 on 04/14/2003 15:54:02

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 15:58:09

quote:

But is it an access thingy that everyone just adopts. Sometime I think that the benefits of access are outweighed by the amount of damage it does (bad designs of the front and back ends). That we get stuck maintaining.

Not an Access thingy for me because I don't use Access ever. I only use parenthesis to make it easier to read, so if I think it can be read easily I don't use them, else I use them.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-14 : 16:11:39

And The Winner Is

ALTER PROCEDURE dbo.zzzzztestWhereDie @Styleselect Int
AS
Declare @Style Varchar(20)

Select dbo.Sub_die .*
From dbo.Sub_die
WHERE C_Style LIKE
Case When @Styleselect = 0 Then '%SF%'
When @Styleselect = 1 Then '%RTE%'
When @Styleselect = 2 Then '%POUCH%'
ELSE '%'
end

Yes I will Get Rid Of The Select * I think Your Right

Thats what I love About this Site, A bit here a bit there and it comes together. Nothing better than Multiple Minds.

Thanks Everyone!!!

Jim

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 16:20:39

I think if you do a SHOWPLAN on that, you'll see a TABLE SCAN btw

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-14 : 16:24:44

Don't stress over parens. makes no difference. SQL is compiled. the follwing should be 100% equivalent and no speed difference whatsoever:

select 1 where (((((1=1)))))

select 1 where 1=1

Trust me, the boolean expressions are completely parsed into some prefix or postfix form (as opposed to infix which we write) and completely shifted and moved around when compiled.

With dynamic SQL i guess you can worry about it, but parenthesize as much as you like, as often as you like, have fun. Whatever makes the code READABLE for you and your co-workers is what is important. If reading "(A and B) and C" is easiser to read than "A and B and C" then write it that way.

EXACTLY the same as:

SELECT Field FROM Table

vs.

SELECT Field
FROM Table

- Jeff

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-15 : 07:39:56

quote:

I think if you do a SHOWPLAN on that, you'll see a TABLE SCAN btw

Brett

8-)

Im Afraid you went right over my head on that Brett.
Ive only been useing SQL for 2 months with no classes I learn Fast But not that fast. Im not familiar with SHOWPLAN or TABLE SCAN.

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources