在mysql中搜索“SanF"时获取旧金山的记录 [英] get records of San Francisco when search for 'SanF' in mysql

问题描述

搜索SanF"时获取旧金山"记录；

Get 'San Francisco' record when I search for 'SanF';

SELECT * FROM table WHERE col LIKE '%san%'; Works
SELECT * FROM table WHERE col LIKE '%san F%'; Works
SELECT * FROM table WHERE col LIKE %sanF%'; Not Working

推荐答案

恐怕LIKE 运算符并不是进行基于文本搜索的查询的灵丹妙药.如果您真的想将 sanF 的用户输入与包含 San Francisco 的记录相匹配，那么您必须从列中删除空格，然后进行比较.>

I'm afraid that the LIKE operator is not a panacea for doing textual search based queries. If you really want to match a user input of sanF to records containing San Francisco, then you will have to remove the whitespace from the column and then do the comparison.

SELECT * FROM table WHERE REPLACE(col, ' ', '') LIKE '%sanF%';

您还可以考虑在应用程序端对用户输入进行一些按摩，以便您原来的两个 LIKE 条件('%san%' 和 '%san F%') 涵盖所有情况.

You could also consider doing some massaging of the user input on the application side so that your original two LIKE conditions ('%san%' and '%san F%') cover every case.

这篇关于在mysql中搜索“SanF"时获取旧金山的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！