Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

Like Operator

I have a database Information with a field Countries having multiple string values separated by commas. eg. India,China,Japan. I have to search for a row which matches any of the country name. Here i used the query "select * from Information where countries like "*India*". This query is not working in because of the like operator. Can anybody give a solution for this.

thanks in advance.


Bekky George
Thursday, November 18, 2004

What does ASP.Net has to do with the Like SQL operator?
I see two possible problems here:
1. You have quotes within quotes (the search word for Like), so you might need to change that to a single quote.
2. Your database provider doesn't support * as a search token for Like, try % instead

GD (
Thursday, November 18, 2004

My solution would be to choose a better data structure - if you have, for example, Indochina, it would get sucked into LIKE '%China%'. LIKE isn't the most efficient of operators. If you need to add or remove countries, you have to basically re-write the entire string.

I'd create a different table with a one-to-many relationship (or, if you wanted to be complicated, a table of countries and a many-to-many relationship).

Thursday, November 18, 2004

If you don't want to bother refactor your solution, try include the preceding and following commas (providing you end the field with a last comma in the DB). I'll enclose strings in my example in {}, so we don't get too many quotes around..:

field content: {India,China,Japan,Indochina,}

Find "China":
{select * from Information where countries like "*,China,*"}

Which in source would be:
sql =
"select * from Information where countries like '*,China,*'";

Or if your DB uses %'s:
sql =
"select * from Information where countries like '%,China,%'";

Tuesday, November 23, 2004

minor nitpick

field content: {,India,China,Japan,Indochina,}

You'll need the preceding comma to access the first record successfully if you're going to include the commas in the search criteria.

Tuesday, November 23, 2004

Right! Call it a typo ;)

Thursday, November 25, 2004

*  Recent Topics

*  Fog Creek Home