mysql详细查询字符串，如通配符 [英] mysql detail query string, like wildcard

问题描述

不确定如何为我的问题加标题...大声笑.

Not sure how to title my question... lol.

下面是我所需要的.

我数据库中的值如下所示:test_example-1，test_example-2，test_example-TD-1

Values in my database look like the following: test_example-1, test_example-2, test_example-TD-1

值的长度可以变化，"test_example-"只是一个例子，其中一些值的名称会有所不同.

The values can vary in length, "test_example-" is just an example, some of the values will have varying names.

当我执行查询以获取所有看起来像"test_example-"的值时，我得到了所有内容，因为我以这种方式使用通配符"test_example-％".但是，我不希望结果中出现"test_example-TD-1".我只想要NON-TD值.因此，如何获得所有的"test_example-"而又不会得到在-"之后仅具有一个整数整数的东西.

When i perform a query to grab all the values that look like this "test_example-" i get everything, because i am using the wild card value this way "test_example-%". However, i do not want the "test_example-TD-1" in the result. I only want the NON-TD values. So, how can i get all the "test_example-" without getting stuff that has more than just a single digit integer after the "-".

数据库中的值在-"之后的位数不得超过一个数字(0-9).因此，基本上我需要一个查询来查找看起来像"test_example- [0-9]"的值的查询.

A value in the database will not have anymore than a single digit number (0-9) after the "-". So basically i need a query that searches for values that would look like this "test_example-[0-9]".

我该怎么做?如果这令人困惑并且需要澄清，请告诉我.谢谢！

How can i do that? If this is confusing and needs clarification, please let me know. Thanks!

这是我为缓解某些混乱而正在执行的确切查询:

Here is the exact query that i am doing to ease some confusion:

SELECT MAC, NAME FROM HOST WHERE NAME LIKE (SELECT CONCAT(LEFT(NAME, LENGTH(NAME)-1), "%") FROM HOST WHERE MAC="some mac address");

---

这是工作结果(感谢swati):

---

Here is the working result ( thanks to swati ):

SELECT MAC, NAME FROM HOST WHERE NAME REGEXP CONCAT(LEFT(NAME, LENGTH(NAME)-1), "[0-9]+") AND MAC="some mac address";

推荐答案

SELECT * FROM `foo` WHERE `value` REGEXP "test_example-[:digit:]+"

这应该适用于test_example- [0-9].有关字符类和REGEXP

That should work for test_example-[0-9]. More on Character Classes and REGEXP

对于您给定的查询，它将是:

With your given query, it would be:

SELECT MAC, NAME FROM HOST WHERE NAME REGEXP CONCAT(LEFT(NAME, LENGTH(NAME)-1), "[0-9]+") AND MAC="some mac address"

请注意，您发布的查询中有一个错误-您有两个WHERE子句.

Note there is an error in your posted query - you have two WHERE clauses.

这篇关于mysql详细查询字符串，如通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！