Mysql：查找列值以特定子字符串结尾的行

I have a column url where 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.

谁能帮我这个？我没有在这里找到答案。

4 个解决方案

#1

I might be oversimplifying... but based on your description, would LIKE solve your problem?

我可能过于简单了......但根据你的描述，会不会解决你的问题？

UPDATE YourTable
SET DifferentColumn = 'NewValue'
WHERE Url LIKE '%.pdf'

#2

You can use a LIKE condition with a leading wildcard. %.pdf means 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'

#3

In your WHERE clause, use something like LOCATE('.pdf',url) > 0 to 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'的任何URL ...

If you definitely want the '.pdf' to be at the end, you can also try:

如果你肯定希望'.pdf'在最后，你也可以尝试：

LOWER(RIGHT(url,4)) = '.pdf'

LOWER（右（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（再次注意大/小写问题）。

#4

So it looks like you are looking for the like parameter.

所以看起来你正在寻找类似的参数。

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.

希望这可以帮助。

#1