mysql - mysql中如何过滤包含特定字母的数据？

Question

我有一个表pub，它看起来如下表:

+------------+----------------------------------+
| pub_id     | pub_name                         |
+------------+----------------------------------+
| P0010101   | Jex Max Publication              |
| P0020101   | BPP Publication                  |
| H0030102   | New Harrold Publication          |
| H0040101   | Ultra Press Inc.                 |
| P0050101   | Mountain Publication             |
| P0060103   | Summer Night Publication         |
| P0070104   | Pieterson Grp. of Publishers     |
| P0080102   | Novel Publisher Ltd.             |
+--------+--------------------------------------+

我想使用 AND 子句进行过滤，其中如果 pub_id 列中的第 1 到第 2 包含 P0整个句子的第 3 到第 6 个字母处的字母和 0101。

我已经尝试过:

SELECT pub_id, pub_name FROM pub WHERE LEFT(pub_id,2) = 'P0' and pub_id LIKE '%0101%'

但是结果是这样的

+------------+----------------------------------+
| pub_id     | pub_name                         |
+------------+----------------------------------+
| P0010101   | Jex Max Publication              |
| P0020101   | BPP Publication                  |
| P0050101   | Mountain Publication             |
+--------+--------------------------------------+

我对结果的期望如下:

+------------+----------------------------------+
| pub_id     | pub_name                         |
+------------+----------------------------------+
| P0010101   | Jex Max Publication              |
+------------+----------------------------------+

Answer 1

您可以只使用pub_id LIKE 'P00101%'。那么你就不需要 left() 了。

Documentation on LIKE