SQL Help: Using LIKE with parameter and wildcard

X

XecutioneR

Active Member
Joined
Nov 4, 2008
Messages
73
Reaction score
0
Hi all

Im using MS SQL 2005, and trying to run a query from VB, using Visual Studio 2008.

What I need is to find all movie information where the title matches text from a textbox, therefore the need for a parameter @Title. From what I googled, I came up with the following:

Select *
From Movies
Where Mov_Title LIKE '%' + @Title + '%'

However, the application disregards the wildcards. Any help on solving this would be appreciated.

Thanks
XecutioneR
 
How about this for a vague idea....

Why not programmatically add the % symbols to the text box entry.. then just do a normal sql query as follows :

Select *
From Movies
Where Mov_Title LIKE @Title
 
Have you tried using ... LIKE "*" + @TITLE + "*"

I remember having to do that with Access years ago, because Microsoft likes to make their own standards. :)

Just a suggestion.
 
Hmmm.. ok.. doing a bit of digging..

Could you paste your entire code snippet, including how you're using the sql query?
 
Shouldn't you be using || instead of
+ to concatenate
 
Sometimes doing the other way around work

i.e.
@Title LIKE ('%' + Mov_Title + '%' )

or add the % signs into @Title seperately before the query. i.e SET @TITLE = '%' + @TITLE + '%' and then use it normally i.e. WHERE Mov_Title LIKE @TITLE
 
The joys of being a SQL person myself..

I don't have to worry how VB or any of those things reinterpret queries.. I write them directly into Management Studio and see if it are working... then move onto the next problem.
 
Thanks for the replies.
Ive tried the last 2 suggestions and had the same results.

Heres the code snippet:
SELECT Mov_MovieID, Mov_Title, Mov_Format, Mov_Category, Mov_RentalPrice, Mov_Genre, Mov_Director, Mov_Actor, Mov_Synopsis, Cpy_MovieCode, Cpy_MovieID,
Cpy_DateAdded, Cpy_CostPrice, Cpy_Status, Cpy_Faulty, Cpy_InactiveDate, Cpy_Comment
FROM MoviesView
WHERE Mov_Title LIKE ('%' + @seTitle + '%')

Im using the same logic for several other tables, and the problem occurs on all. The reason I need to use LIKE instead of = is so the user does not have to enter the entire movie title. Im open to any other suggestions that would offer the same functionality.
 
the code above is the entire structure of a view that I'm calling. But I'm trying to get similar functionality on other aspects of the program as well, all of which use the same search function
 
Sorry.. worded horribly...

How many rows is the table that you're calling?
 
Currently there just over 10 but I'm testing by trying to search for a part of a name. Eg. If alice is in the database, it should find when the user types ali
 
This may sound odd, but you need to kind of figure out an upper bounds in terms of number of rows for the tables you're searching....

cos you could pull the whole table into memory on the machine and then do your searching like that...

Its not efficient at all, but I'd guess its a whole lot easier to code, plus you could do some funky autocomplete stuff that way as well.
 
Something is very odd. All the things you've tried so far would normally work. Does this work:

WHERE CHARINDEX(LOWER(@seTitle), LOWER(Mov_Title)) != 0
 
Havent tried that yet, but case doesnt matter. When I run the search for testing I use all lower caps but it picks up entries regardless of the caps
 
ok, so your db is set to 'CI' - cool exclude the LOWER() calls - but does using CHARINDEX instead of like work?
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X