SQL LIKE operator not returning all records

Question

_{Wednesday, September 24, 2008 2:43 PM}

Hi guys,

New to asp.net and web programming in general so I have been working through asp.net's tutorials, but whilst diverging from them slightly I have hit a problem.

 

I have a Dataset with a table adapter that has the following query:

SELECT     UserId, Address
FROM         Users
WHERE     (Address LIKE @city)

Now, one of the Records has Houston in the address (In fact all address are just city names) but I find that if I supply %oust% that it does not return the record, but if I provide %ousto% (Or any other 5 characters in the sequence) it does return the record.

If I execute the same query in the Query builder or  SQL Studio Express it returns the record.

My apologies if some of the terminology isn't quite right.

Thanks in advance for any help.

 

 

All replies (12)

_{Thursday, September 25, 2008 12:50 AM ✅Answered}

 change that query little bit

SELECT     UserId, Address
FROM         users
WHERE     (Address LIKE '%' + @city + '%')

 

now when passing parameter, don't concatenate with %. just pass that word only.

string srch =  txtSearch.Text ;

---

_{Thursday, September 25, 2008 4:31 AM ✅Answered}

 Both are same. but i wants to check whether you are concatenating those % symbols correctly.

Check your  SQL Profiler, whether the parameters passed correctly or not.

you can find profiler in 

 start->all programs -> sql server 2005 -> performance tools -> sql server profiler.

give the credentials and start the trace. Now run the application and run this page and search with the word "oust".

then come and see this trace. see the running queries. find that query and check whether parameter passed correctly or not.

and then run that query in you sql management studio. 

---

_{Wednesday, September 24, 2008 2:54 PM}

 SELECT     UserId, Address
FROM         Users
WHERE     (Address LIKE '"%@city%"')

---

_{Wednesday, September 24, 2008 2:56 PM}

SELECT     UserId, Address
FROM         Users

WHERE Address LIKE '%'+ @city + '%'

---

_{Wednesday, September 24, 2008 3:05 PM}

Generally we use % when we use Like Operator. '%' means that It returns all the words which start or end with the character you mentioned in between %.

 

Ex:  Selecr UserID, Address From Users where Address Like '%' +@city + '%'

---

_{Wednesday, September 24, 2008 3:45 PM}

I do use wildcards, hence as I put in my original post:

%ousto%   returns Houston

%oust%  doesn't

Therefore I am performing the following:

.GetUserByAddr("%" + "oust" + "%");

Incidentally, if I use .GetTeamsByAddr("%%" + "oust" + "%%");

It does then work.

---

_{Wednesday, September 24, 2008 4:32 PM}

Wildcard % matches any string of zero or more characters.

You may find more information from this link:

http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx

---

_{Wednesday, September 24, 2008 6:13 PM}

That's what I thought.

So, forgive me if I am being really dumb, but shouldn't '%oust%' return the record with Houston then, as I had anticipated, and as it does in the Query Execute of the DataSet?

---

_{Wednesday, September 24, 2008 6:44 PM}

Can you post the code in the GetUsersByAddr method.

---

_{Wednesday, September 24, 2008 7:08 PM}

 It's not a user method, it's part of a typed DataSet table adapter, the QUERY is:

SELECT     UserId, Address
FROM         users
WHERE     (Address LIKE @city)

And it's called via

protected void butTeamSearch_Click(object sender, ImageClickEventArgs e)
    {
        if(txtSearch.Text != "")
        {
            string srch = "%" + txtSearch.Text + "%";

            dsUsersTableAdapters.usersTableAdapter adapter = new dsUsersTableAdapters.usersTableAdapter();
            dsUsers.usersDataTable userlist;
            userlist = adapter.GetUsersByAddress(srch);

Where txtSearch.Text = "oust" it fails to yield the Houston record, but "ousto" does. :/

Again, apologies if my terminology is 100%.

---

_{Thursday, September 25, 2008 4:18 AM}

I tried that I'm afraid (as part of the processes of working back from not supplying any parameter to all of the parameter after hitting the problem) and it yielded the same results. :(

Out of interest, why do you think/does that differ from the other approach, where the wildcards are also passed as part of the parameter?

---

_{Thursday, September 25, 2008 8:48 AM}

I can do this when I get home, though I don't recall seeing profile tools.

That said though, bare in mind that I mentioned above that the query does work in SQL Man. Studio and in the query execute of the Table Adapter, it just fails when calling via the code above.