Search code examples
phpsqlstringsql-likerelevance

How to search a string specifically in middle of another string

Searching substring in all columns of the table.Result to be displayed in following order:
1.first results starting with substring.
2.Then results in which substring in middle and not at last.
3.Then results in which substring is at last.
The problem arises when searching for substring in middle and not at last because like %substring% will also contain %substring which is actually at last.So the order is not preserved. 

foreach($results as $r){
$append1 ="select * from ".$tablename." WHERE ".$r['COLUMN_NAME']." like'".$substring."%'union ";
$query1 = $query1.$append1;
}  
foreach($results as $r){
$append2 ="select * from ".$tablename." WHERE ".$r['COLUMN_NAME']." like'%".$substring."%'union";
$query2 = $query2.$append2;
}
foreach($results as $r){
$append3 ="select * from ".$tablename." WHERE ".$r['COLUMN_NAME']." like'%".$substring."'union";
$query3 = $query3.$append3;
}
$query4=$query1.$query2.$query3;
$query4 = substr($query4, 0, -5);
Solution

  • The LIKE operator uses two wildcard characters. You already know about %, but there's also _ (which basically means "exactly one of any character").

    So you can do the second query like

    $append2 ="SELECT * FROM $tablename WHERE {$r['COLUMN_NAME']} LIKE '_%{$substring}%_' union";

    and it should only match where there's at least one character before $substring and one after.