CODEWITHSUNDEEP
phpmysql
Amit Sharma
Amit Sharma

Reputation: 192

sql query where like clause adding extra space in value

I am searching a keyword in DB that is Bootjack Fire and Rescue Foundation As there is single space after a word, my query is `

WHERE `title` LIKE  '%Bootjack Fire and  Rescue Foundation%'

Why are there two space after the and keyword in the search title as you can see in the query?

add query 

$keyword = 'Bootjack Fire and Rescue Foundation';

$this->db->select('stores.id,store_title,store_category_id,store_sub_category_id,store_sub_category_type_id,sub_category_fourth,store_link');
$this->db->from('stores');
$this->db->join('users','users.id=stores.user_id');
$this->db->join('store_category','stores.store_category_id=store_category.id');

$where="`store_title` LIKE  '%".$keyword."%'";
$this->db->where($where);
$this->db->where('store_status', 1);
$this->db->where('store_type', 2);
$this->db->where('store_category.status', 1);
$this->db->order_by('store_title','ASC');
$this->db->order_by('store_category_id');

Upvotes: 0

Views: 894

Answers (2)

Avishek
Avishek

Reputation: 824

You can convert multiple space into a single one then use the same like clause.

$where="replace(replace(replace(store_title,' ','<>'),'><',''),'<>',' ') LIKE  '% replace(replace(replace(".$keyword".,' ','<>'),'><',''),'<>',' ') %'";

considering < and > symbol will not appear in that column value, otherwise need to use some other symbols. I have used this in SQL-SERVER should works in Mysql also.

You can remove multiple spaces in php like below...

$keyword=preg_replace('/\s+/', ' ',$keyword);

then use the simple sql as...

$where="store_title LIKE '% ".$keyword" %'";

Upvotes: 1

Daniel
Daniel

Reputation: 150

I think is not possible directly in the query.

You can transform your keyword to repair any wrong data.

I hope it helps you.

Upvotes: 1

Related Questions