SQL Help: Using LIKE with parameter and wildcard

Just to be different, I would probably have done a soundex; as previous posters stated, the original query should work, but seeing as you got it to work, probably doesn't matter.

How I would do it ?

SELECT * FROM Whatever where SOUNDEX(Title) = SOUNDEX(@Param)

Not TransAct compatable, but the one advantage here is it can be done a lot more scalable than doing like.

Like will force table scans, can't do it any other way.

With SoundEx you could create a trigger or just manually update a separate field and store the soundex value in the table; Then you can use indexes on that field and voila -- your DB still flies even with millions of rows ...
 
select * from X is wrong. learn it now and start to type out the column names.
 
Necuno said:
select * from X is wrong. learn it now and start to type out the column names.
Click to expand...

Agreed; and there is also probably not a Whatever table -- but I'm lazy and my point is on the where clause not the select clause.

But yes -- 100% correct.
 
XecutioneR said:
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
Click to expand...

did you run the query before hand in sql 2005 management, using the table and database without connection via your application ? because looking at your query it should work unless you are not passing the parameters through to sql correctly or something else is wrong. this is why imho it's always good to test your query first directly on the table using sql management studio and then implement it.

example (but i see yours is the same way)

Code: 
SELECT
	Title
,	Lastname
,	PreferredName
FROM
	Employee
WHERE
	Lastname like '%'+ @Lastname +'%'

@Lastname is obviously declared above the select and set via "the caller" with a value.

XecutioneR said:
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.
Click to expand...

i would have to be daft here and ask if a view is not a bit heavy handed if you want to do suggestions based on a title search. do the end user really need to see 100% of the info if he only wants "suggested" on his criteria being maybe a title, genre and small synopses ?
 
Last edited:
I agree with Necuno here. Could you maybe post the code performing the database call and query execution - it might be that something is going awry there. The query itself looks 100%.
 
Hey guys. So far the CHARINDEX has been working fine for all except one case. The behaviour is similar to the LIKE statement not working when you are searching many fields. Heres the code below:

SELECT Acc_AccountNum, Acc_AddL1, Acc_AddL2, Acc_AddL3, Acc_Balance, Acc_HomeTele, Acc_PostCode, Cus_AccHolder, Cus_AccountNum,
Cus_Cellphone, Cus_CusNum, Cus_Flag, Cus_ID, Cus_Name, Cus_Note, Cus_Surname
FROM CustomersView
WHERE (Cus_CusNum = @SeCusNum) OR
(CHARINDEX(@SeNameSurID, Cus_Name) <> 0) OR
(CHARINDEX(@SeNameSurID, Cus_Surname) <> 0) OR
(Cus_ID = @SeNameSurID)
 
With regards to the other notes.

1. I used * here to make the code snippet shorter when I posted
2. At this point Im just getting fuctionality working so I havent omitted fields that are not required (yes, im lazy), therefore the statement above includes all fields in the sql statement. But on the vb design side, I just drag the fields from the datasource I require on to the form, thus the user doesnt see everything thier not supposed to
3. I havent run the query in SQL Management studio, however I did run the execute query in visual studio at the query builder screen. Im assuming that at this point, the result would have been the same. Note that I said I assumed this is the same
 
Depending on your requirements, I would still suggest that you rather look at SOUNDEX.

As other posters suggested, both LIKE and CHARINDEX does work. Seriously, it does - if yours doesn't then something is wrong on your side, not the database, sorry.

And as another poster suggested, and this I can agree with, and having some experience with databases professionally -- always start with your query and run it through some query analyser, SQL Management Studio or whatever.

Step 1A:

Confirm your query works and you get the results you expect. Do this via Query Analyzer or SQL Management Studio or some other tool that can directly query your database.

Step 1B:

For extra marks, enable your query plan and actually see what work your database does

Step 2:

Call your query from your code.

Step 3:

Go out and have a beer.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X