Using LIKE without %

[forces1]

For a searchfunction on my site, I use the LIKE-method to get the right information out of my database. I've broken up the searchterm into apart words, and they have become the $ucTerms variables.

if (!$ucTerms[1]){
  my $sth = $dbh->prepare("SELECT * FROM metalink WHERE `inhoud` LIKE '%$ucTerms[0]%'") || print "could not access database";
  $sth->execute();
  while (my $results = $sth->fetchrow_hashref) {
    my $linkid = $results->{id};
    my $url = $results->{url};
	my $titel = $results->{titel};
    my $inhoud = $results->{inhoud};
    $deelsearchresults{$linkid} = "$linkid|$url|$titel|$inhoud";
  } $sth->finish;
}
elsif (!$ucTerms[2]){
  my $sth = $dbh->prepare("SELECT * FROM metalink WHERE `inhoud` LIKE '%$ucTerms[0]%' AND `inhoud` LIKE '%$ucTerms[1]%')") || print "could not access database";
  $sth->execute();
  while (my $results = $sth->fetchrow_hashref) {
    my $linkid = $results->{id};
    my $url = $results->{url};
	my $titel = $results->{titel};
    my $inhoud = $results->{inhoud};
    $deelsearchresults{$linkid} = "$linkid|$url|$titel|$inhoud";
  } $sth->finish;
}
elsif (!$ucTerms[3]){
  my $sth = $dbh->prepare("SELECT * FROM metalink WHERE `inhoud` LIKE '%$ucTerms[0]%' AND `inhoud` LIKE '%$ucTerms[1]%')") AND `inhoud` LIKE '%$ucTerms[2]%')") || print "could not access database";
  $sth->execute();
  while (my $results = $sth->fetchrow_hashref) {
    my $linkid = $results->{id};
    my $url = $results->{url};
	my $titel = $results->{titel};
    my $inhoud = $results->{inhoud};
    $deelsearchresults{$linkid} = "$linkid|$url|$titel|$inhoud";
  } $sth->finish;
}
elsif (!$ucTerms[4]){
  my $sth = $dbh->prepare("SELECT * FROM metalink WHERE (`inhoud` LIKE '%$ucTerms[0]%' AND `inhoud` LIKE '%$ucTerms[1]%' OR `inhoud` LIKE '%$ucTerms[2]%' OR `inhoud` LIKE '%$ucTerms[3]%')") || print "could not access database";
  $sth->execute();
  while (my $results = $sth->fetchrow_hashref) {
    my $linkid = $results->{id};
    my $url = $results->{url};
	my $titel = $results->{titel};
    my $inhoud = $results->{inhoud};
    $deelsearchresults{$linkid} = "$linkid|$url|$titel|$inhoud";
  } $sth->finish;
}

Now everything works fine, but when I remove the % (because that's what I want, I want to search for the exact words, not words containing the variable) it doesn't work anymore. My question is: can't I use the LIKE without the %? And what can I use to make it work then?

 

[fishiface]

if you want to search for the exact words, why not use = instead of LIKE?

You don't say what database you are using, so I can't be specific, but it also sounds like you are running into problems with trailing spaces in datavalues.

You may be able to set trailing space truncation on your database connection or use a function like TRIM() or TRUNC() in the SQL to circumvent this.

Yours,

fish

["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life was going to be spent in finding mistakes in my own programs.["]
--Maurice Wilkes, 1949

 

[PaulTEG]

This would be more a MYSQL question than a perl question

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[travs69]

I think you must have %'s if you use like (more of a SQL question than a perl question).. how about this

'% $ucTerms[0] %'

so by putting spaces around $ucTerms[0] it won't find words that contain that variable because it will also have to match the spaces. It also won't match the word if it is at the end of a sentence because of the punctuation after the word. If you don't get the answer here I would take it to the SQL forums.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[forces1]

Sorry guys that I've put it in the wrong forum, you are absolutly right. But thanks for answering my question, I will try it.

 

[MillerH]

This is probably a little late to be jumping in on this question. Nevertheless, there are a few issues that I would like to point out with your code:

1) You used a lot of code replication. Instead of having an if statement around the entire query processing, just use the if for determining the $sql statement. From there, all the programming is the same. Even better, since each of these values is treated the same (it's just a question of quantity), let's use a grep and avoid the need for an if condition at all.
2) Let DBI handle your error messages with errstr. Also, a simple print statement is rarely helpful. Use a die so you get line number reporting.
3) Your values do not escape the special LIKE statement characters of % and _.
4) Your values do not use basic escaping for string values. IE, the \ and '. This is actually a huge security hole in your statements.
5) You've misspelled "title" as "titel". At least I'm assuming this is a typo.

Now normally when solving problem 4, I would just advise someone to use placeholders so that DBI could handle the escaping for you. However, problem 3 introduces a twist in that I do not think it will automatically handle the escaping of string comparison special characters. (I'm going to test this later) For that reason we must stick with building the statement with values in place and escaped.

Here is how I would rewrite your code:

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]@terms[/blue] = [url=http://perldoc.perl.org/functions/grep.html][black][b]grep[/b][/black][/url] [red]{[/red][url=http://perldoc.perl.org/functions/defined.html][black][b]defined[/b][/black][/url] && [blue]$_[/blue] ne [red]'[/red][purple][/purple][red]'[/red][red]}[/red] [blue]@ucTerms[/blue][red];[/red]

[olive][b]if[/b][/olive] [red]([/red]! [blue]@terms[/blue][red])[/red] [red]{[/red]
	[url=http://perldoc.perl.org/functions/warn.html][black][b]warn[/b][/black][/url] [red]"[/red][purple]User specified no data[/purple][red]"[/red][red];[/red]
	
[red]}[/red] [olive][b]else[/b][/olive] [red]{[/red]
	[gray][i]# Build SQL Statement[/i][/gray]
	[black][b]my[/b][/black] [blue]$clause[/blue] = [url=http://perldoc.perl.org/functions/join.html][black][b]join[/b][/black][/url] [red]'[/red][purple] OR [/purple][red]'[/red], [url=http://perldoc.perl.org/functions/map.html][black][b]map[/b][/black][/url] [red]{[/red]
		[red]"[/red][purple]`inhoud` LIKE '%[/purple][red]"[/red] . [maroon]escape_mysql_like[/maroon][red]([/red][blue]$_[/blue][red])[/red] . [red]"[/red][purple]%'[/purple][red]"[/red]
	[red]}[/red] [blue]@terms[/blue][red];[/red]
	[black][b]my[/b][/black] [blue]$sql[/blue] = [red]"[/red][purple]SELECT * FROM metalink WHERE [blue]$clause[/blue][/purple][red]"[/red][red];[/red]
	
	[black][b]my[/b][/black] [blue]$sth[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][blue]$sql[/blue][red])[/red][red];[/red]
	[blue]$sth[/blue]->[maroon]execute[/maroon] or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [blue]$dbh[/blue]->[maroon]errstr[/maroon][red];[/red]
	
	[olive][b]while[/b][/olive] [red]([/red][black][b]my[/b][/black] [blue]$results[/blue] = [blue]$sth[/blue]->[maroon]fetchrow_hashref[/maroon][red])[/red] [red]{[/red]
		[blue]$deelsearchresults[/blue][red]{[/red][blue]$results[/blue]->[red]{[/red]id[red]}[/red][red]}[/red] = [black][b]join[/b][/black] [red]'[/red][purple]|[/purple][red]'[/red], [blue]@[/blue][red]{[/red][blue]$results[/blue][red]}[/red][red]{[/red][red]qw([/red][purple]id url title inhoud[/purple][red])[/red][red]}[/red][red];[/red]
	[red]}[/red]

	[blue]$sth[/blue]->[maroon]finish[/maroon][red];[/red]
[red]}[/red]

[url=http://perldoc.perl.org/functions/sub.html][black][b]sub[/b][/black][/url] [maroon]escape_mysql_like[/maroon] [red]{[/red]
	[black][b]my[/b][/black] [red]([/red][blue]$text[/blue][red])[/red] = [blue]@_[/blue][red];[/red]
	[blue]$text[/blue] =~ [red]s{[/red][purple]([%_'[purple][b]\\[/b][/purple]])[/purple][red]}[/red][red]{[/red][purple][purple][b]\\[/b][/purple][blue]$1[/blue][/purple][red]}[/red][red]g[/red][red];[/red]
	[url=http://perldoc.perl.org/functions/return.html][black][b]return[/b][/black][/url] [blue]$text[/blue][red];[/red]
[red]}[/red]

- Miller