dpm91915
2016-12-11 20:26
浏览 366
已采纳

如何用LIKE忽略mysql搜索查询中的空格?

I am working on a program where a user searches for a "course" by course code or name. I am trying to make it so if the user searches "CODE1002", CODE 1002 EL from the database will still return (there is a space). As indicated by other posts I have tried to use the REPLACE function with no success.

Here is my current code (however it does not ignore spaces):

$records = $conn->prepare('SELECT (SELECT COUNT(*) FROM courses WHERE (course_code LIKE :searchText OR course_name LIKE :searchText)) AS course_count, course_id, course_code, course_name FROM courses WHERE (course_code LIKE :searchText OR course_name LIKE :searchText) ORDER BY course_code LIMIT 6');

$final_search_term = "%".$search_term."%";
$records->bindParam(':searchText', $final_search_term);

Here is my attempt at adding REPLACE to ignore spaces on the course_code with no success:

$records = $conn->prepare('SELECT (SELECT COUNT(*) FROM courses WHERE (REPLACE(course_code,' ','') LIKE :searchText OR course_name LIKE :searchText)) AS course_count, course_id, course_code, course_name FROM courses WHERE (REPLACE(course_code,' ','') LIKE :searchText OR course_name LIKE :searchText) ORDER BY course_code LIMIT 6');

$final_search_term = "%".$search_term."%";
$records->bindParam(':searchText', $final_search_term);

The first code works perfectly aside from not ignoring spaces.

图片转代码服务由CSDN问答提供 功能建议

我正在开发一个程序,用户按课程代码或名称搜索“课程”。 我试图这样做,如果用户搜索“CODE1002”,数据库中的CODE 1002 EL仍将返回(有空格)。 正如其他帖子所示,我尝试使用REPLACE函数但没有成功。

这是我当前的代码(但它不会忽略空格): < / p>

  $ records = $ conn-&gt; prepare('SELECT(SELECT COUNT(*)FROM courses WHERE(course_code LIKE:searchText OR course_name LIKE:searchText))AS course_count,course_id  ,course_code,course_name FROM courses WHERE(course_code LIKE:searchText OR course_name LIKE:searchText)ORDER BY course_code LIMIT 6'); 
 
 $ final_search_term =“%”。$ search_term。“%”; 
 $ records-&gt  ; bindParam(':searchText',$ final_search_term); 
   
 
 

这是我尝试添加REPLACE以忽略course_code上的空格而没有成功:< / strong>

  $ records = $ conn-&gt; prepare('SELECT(SELECT COUNT(*)FROM courses WHERE(REPLACE(course_code,'','')LIKE  :searchText OR course_name LIKE:searchText))AS course_count,course_id,course_code,course_name FROM courses WHERE(REPLACE(course_code,'','''  )LIKE:searchText OR course_name LIKE:searchText)ORDER BY course_code LIMIT 6'); 
 
 $ final_search_term =“%”。$ search_term。“%”; 
 $ records-&gt; bindParam(':searchText',  $ final_search_term); 
   
 
 

除了不忽略空格外,第一个代码完美无缺。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanci1939 2016-12-11 20:30
    已采纳

    You are putting the replace on the wrong side. Just to be safe, let's do it for both operands:

    REPLACE(course_code, ' ', '') LIKE REPLACE(:searchText, ' ', '')
    
    点赞 评论

相关推荐 更多相似问题