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.
| Author |
Topic |
|
echovault
Starting Member
20 Posts |
 Posted - 2008-01-30 : 10:42:20
|
Is it possible to use LIKE and NOT LIKE conditionally? For example I want to have a stored procedure that accepts a filter string and an include/exclude bit, that indicates whether to filter by excluding the passed in filter string or including it. I know this is syntactically wrong, but I think it conveys the idea. DECLARE @UserFilter varchar(20), @Include bitSELECT @UserFilter = COALESCE(@UserFilter, '%')SELECT * FROM Users U WHERE CASE @Include WHEN 1 THEN U.ID LIKE @UserFilter ELSE U.ID NOT LIKE @UserFilterEND I want to avoid dynamic sql for this procedure since the ending result will be used recursively in a CTE. (Let me know if anyone wants to see that)Just wondering if there is a pure-sql method of conditionally specifying LIKE vs NOT LIKE.I've tried many combinations of the above, and it seems like it should be there, just not quite grasping it.I've also looked all over the net, and I have found solutions for conditional filtering, but only for including, not conditionally including or excluding.Thanks in advance to all the experts! |
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-01-30 : 10:47:19
|
| WHERE ( @Include = 1 AND U.ID LIKE @UserFilter OR @Include = 0 AND U.ID NOT LIKE @UserFilter ) |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-01-30 : 10:48:24
|
Something like this may work:SELECT * FROM Users U WHERE @Include = 1 and U.ID like @UserFilterUNION ALLSELECT * FROM Users U WHERE @Include <> 1 and U.ID not like @UserFilter Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
echovault
Starting Member
20 Posts |
Posted - 2008-01-30 : 10:56:28
|
| Thanks jdaman, that will work perfectly. I had a feeling I was straining too hard at this one. Thanks again to everyone, this place rocks. |
|
|
|
|
|
|
Conditionally use LIKE vs NOT LIKE