sql query where like clause adding extra space in value

Question

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');

update the question to include the code that creates the query — Alex Andrei
– Alex Andrei, Commented Jul 19, 2016 at 10:31
I don't see anything in your code that would add an extra space after and, unless your db class does it. Is that a class of your own or a 3rd-party library? — Barmar
– Barmar, Commented Jul 19, 2016 at 10:56
No its simple search that add one more space after and keyword. And I am really shocked — Amit Sharma
– Amit Sharma, Commented Jul 19, 2016 at 11:00
Show the code for $this->db->where(), because that's what seems to be doing it. — Barmar
– Barmar, Commented Jul 19, 2016 at 11:04

Daniel · Accepted Answer · 2016-07-19 10:44:00Z

1

I think is not possible directly in the query.

You can transform your keyword to repair any wrong data.

Split the phrase -- http://php.net/manual/en/function.str-split.php
Trim all words to remove whitespace. -- http://php.net/manual/en/function.trim.php
Re-create the phrase correctly with one space.

I hope it helps you.

answered Jul 19, 2016 at 10:44

Daniel

1501 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Amit Sharma Over a year ago

I can do this but I want exact match and this is the first time that i have seen this issue that taking double spaces after and word

Barmar Over a year ago

The keyword variable doesn't have wrong data. He's claiming that the space gets added when it gets put into the query.

Daniel Over a year ago

Ok @Barmar, I did not understand the question correctly

Avishek · Accepted Answer · 2016-07-19 11:25:02Z

1

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" %'";

edited Jul 19, 2016 at 11:25

answered Jul 19, 2016 at 10:38

Avishek

8141 gold badge7 silver badges20 bronze badges

2 Comments

Amit Sharma Over a year ago

oops o/p is same "WHERE replace(replace(replace(store_title,' ','<>'),'><',''),' <> ',' ') LIKE '%Bootjack Fire and re%' " as you can see double spaces after and word

Barmar Over a year ago

The extra space is in the query, not the table data.

Collectives™ on Stack Overflow

sql query where like clause adding extra space in value

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related