无法匹配SQL表中的单个字符 [英] Having trouble matching a single character in an SQL table

问题描述

我需要使用'_'通配符来查找所有仅包含一个字母的id.但是，当我运行查询时，不会返回任何行.

I need to use the '_' wildcard to find all id that are only one letter which there are a few of. However when I run my query no rows are returned.

在这里查询:

SELECT *
FROM table
WHERE id LIKE '_';

我有一个表，可以调用具有两列(id和name)的Table1. id可以包含1个或2个字符来标记名称.我正在尝试只找到id仅为一个字符的名称.这是表格的示例:

I have a table lets call Table1 that has two columns, id and name. id either has 1 or 2 characters to label a name. I'm trying to only find the names where the id is only one character. Heres an example of the table:

id     name
A      Alfred
AD     Andy
B      Bob
BC     Bridget

在此示例中，我只想返回Alfred和Bob.

I only want to return Alfred and Bob in this example.

我不想要解决方案，但是任何建议或想法都会有所帮助.

I don't want the solution but any advice or ideas would be helpful.

这是我的查询的屏幕截图: http://i.imgur.com/EWTfoVI.png?1

Here is a screenshot of my query: http://i.imgur.com/EWTfoVI.png?1

这是我的桌子的一个小例子: http://i.imgur.com/urGRZeK.png?1

And here is a small example of my table: http://i.imgur.com/urGRZeK.png?1

因此，在我的表格的这个示例中，理想情况下，我只希望返回东亚....

So in this example of my table I would ideally like only East Asia... to be returned.

如果我专门搜索它可以工作的字符，但由于某种奇怪的原因，'_'通配符就不起作用.

I if I search specifically for the character it works but for some strange reason the '_' wildcard doesn't.

例如:

SELECT *
FROM icao
WHERE prefix_code ='Z';

这有效.

推荐答案

在MySQL中，下划线用于表示单个字符的通配符.您可以在此处模式匹配了解更多信息.

In MySQL, the underscore is used to represent a wildcard for a single character. You can read more about that Pattern Matching here.

以这种方式编写，查询将拉出id列仅是一个字符的任何行，而无需更改任何内容.

The way you have it written, your query will pull any rows where the id column is just one single character, you don't need to change anything.

这是一个 SQL小提琴的示例.

编辑

一个解决问题的技巧是确保前缀代码前后没有空格.如果有，并且需要将其删除，请添加TRIM():

One trouble shooting tip is to be sure there is no whitespace before/after the prefix code. If there is, and you need to remove it, add TRIM():

SELECT *
FROM myTable
WHERE TRIM(id) LIKE '_';

以下是 TRIM 的示例.

编辑2

希望能对您的怪异行为做出一些解释.在MySQL中，如果字符上有尾随空格，那么如果您说id = 'Z';仍然与之匹配，如

A little explanation to your weird behavior, hopefully. In MySQL, if there is trailing white space on a character, it will still match if you say id = 'Z'; as seen by this fiddle now. However, leading white space will not match this, but will still be corrected by TRIM(), because that removes white space on the front and back end of the varchar.

TL; DR Z后面有空白，这是造成问题的原因.

TL;DR You have trailing white space after Z and that's causing the problem.

这篇关于无法匹配SQL表中的单个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！