Mysql：查找列值以特定子字符串结尾的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15345400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql: Find rows where column value ends with a specific substring
提问by Phil
I have a column urlwhere all the values are urls. I'm trying to update the value of a different column where-ever the aforementioned url ends with .pdf.
我有一列url，其中所有值都是 url。我正在尝试更新不同列的值 - 无论上述 url 以.pdf.
Can anyone help me with this? I haven't found an answer here on SO.
谁能帮我这个？我还没有在 SO 上找到答案。
回答by Michael Fredrickson
回答by Matt Busche
You can use a LIKE condition with a leading wildcard. %.pdfmeans the column must end in .pdf
您可以使用带有前导通配符的 LIKE 条件。%.pdf意味着该列必须以.pdf
UPDATE mytable
SET newcolumn = 'PDF found'
WHERE yourcolumn LIKE '%.pdf'
Alternatively you could use the right()function
或者，您可以使用该right()功能
UPDATE mytable
SET newcolumn = 'PDF found'
WHERE right(yourcolumn,4) = '.pdf'
回答by Marc Audet
In your WHERE clause, use something like LOCATE('.pdf',url) > 0to identify the record of interest.
在您的 WHERE 子句中，使用类似的内容LOCATE('.pdf',url) > 0来标识感兴趣的记录。
As pointed out, this will give you any url that contains '.pdf' in the string...
正如所指出的，这将为您提供字符串中包含“.pdf”的任何网址...
If you definitely want the '.pdf' to be at the end, you can also try:
如果您肯定希望将 '.pdf' 放在最后，您也可以尝试：
LOWER(RIGHT(url,4)) = '.pdf'
LOWER(RIGHT(url,4)) = '.pdf'
I would use LOWER or UPPER to deal with any case issues.
我会使用 LOWER 或 UPPER 来处理任何案例问题。
You can also use LIKE %.pdf(again, watch out for upper/lower case issues) as suggested
by others.
您也可以LIKE %.pdf按照其他人的建议使用（再次注意大写/小写问题）。
回答by Mic1780
So it looks like you are looking for the likeparameter.
所以看起来你正在寻找类似的参数。
for example:
例如：
SELECT column(s) FROM table WHERE URL LIKE '%.pdf'
Or you can update with
或者你可以更新
UPDATE table SET column = value, ..., WHERE URL LIKE '%.pdf'
The % is like saying anything before the .pdf is htis case.
% 就像在 .pdf 是 htis 案例之前说任何话一样。
Hope this helps.
希望这可以帮助。
