与SQL LIKE '%value%&#39的倒数。[英] Inverse of SQL LIKE '%value%'

本文是小编为大家收集整理的关于与SQL LIKE '%value%&#39的倒数。的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个包含域名的mySQL表:

+----+---------------+
| id | domain        |
+----+---------------+
|  1 | amazon.com    |
|  2 | google.com    |
|  3 | microsoft.com |
|    |     ...       |
+----+---------------+

我希望能够浏览该表格中的完整主机名(即'www.google.com').如果是桌子上包含完整URL的另一种方式,我将使用:

SELECT * FROM table WHERE domain LIKE '%google.com%'

但逆不太简单.我目前的想法是搜索完整的主机名,然后逐步剥离域的每个部分,然后再次搜索. (即搜索'www.google.com'然后'google.com')

这不是特别有效或聪明的,必须有更好的方法.我确定这是一个常见的问题,毫无疑问很容易解决!

推荐答案

您也可以使用类似右侧的列:

SELECT domain FROM table WHERE 'www.google.com' LIKE CONCAT('%', domain);

SELECT domain FROM table WHERE 'www.google.com' LIKE CONCAT('%', domain, '%');

它不是特别有效,但它起作用.

其他推荐答案

mysql您可以使用正则表达式(RLIKE)进行匹配.鉴于此功能,您可以做这样的事情:

SELECT * FROM table WHERE 'www.google.com' RLIKE domain;

似乎已经实现了RLIKE的方式,甚至足够聪明,可以将该领域的点(通常是regex中的通配符)作为字面的点.

MySQL's纳入正则表达式可为您提供分析和搜索字符串的强大能力.如果您想进一步了解正则表达式,只需Google" regex".您也可以使用以下链接之一:

http://en.wikipedia.org/wiki/wiki/regular_expression

http://www.regular-expressions.info/

http://www.codeproject.com/kb/kb/string/re.aspx

其他推荐答案

您可以使用一些SQL字符串操纵来生成等效的字符串.Endswith():

SELECT * FROM table WHERE  
substring('www.google.com',
len('www.google.com') - len([domain]) ,
len([domain])+1) = [domain]

本文地址:https://www.itbaoku.cn/post/597688.html

问题描述

I have a MySQL table containing domain names:

+----+---------------+
| id | domain        |
+----+---------------+
|  1 | amazon.com    |
|  2 | google.com    |
|  3 | microsoft.com |
|    |     ...       |
+----+---------------+

I'd like to be able to search through this table for a full hostname (i.e. 'www.google.com'). If it were the other way round where the table contained the full URL I'd use:

SELECT * FROM table WHERE domain LIKE '%google.com%'

But the inverse is not so straightforward. My current thinking is to search for the full hostname, then progressively strip off each part of the domain, and search again. (i.e. search for 'www.google.com' then 'google.com')

This is not particular efficient or clever, there must be a better way. I am sure it is a common problem, and no doubt easy to solve!

推荐答案

You can use the column on the right of the like too:

SELECT domain FROM table WHERE 'www.google.com' LIKE CONCAT('%', domain);

or

SELECT domain FROM table WHERE 'www.google.com' LIKE CONCAT('%', domain, '%');

It's not particularly efficient but it works.

其他推荐答案

In mysql you can use regular expressions (RLIKE) to perform matches. Given this ability you could do something like this:

SELECT * FROM table WHERE 'www.google.com' RLIKE domain;

It appears that the way RLIKE has been implemented it is even smart enough to treat the dot in that field (normally a wildcard in regex) as a literal dot.

MySQL's inclusion of regular expressions gives you a very powerful ability to parse and search strings. If you would like to know more about regular expressions, just google "regex". You can also use one of these links:

http://en.wikipedia.org/wiki/Regular_expression

http://www.regular-expressions.info/

http://www.codeproject.com/KB/string/re.aspx

其他推荐答案

You could use a bit of SQL string manipulation to generate the equivalent of string.EndsWith():

SELECT * FROM table WHERE  
substring('www.google.com',
len('www.google.com') - len([domain]) ,
len([domain])+1) = [domain]