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.
Can anyone help me with this? I haven't found an answer here on SO.
4 Answers
I might be oversimplifying... but based on your description, would LIKE solve your problem?
UPDATE YourTable
SET DifferentColumn = 'NewValue'
WHERE Url LIKE '%.pdf'
answered Mar 11, 2013 at 18:06
Michael Fredrickson
37.5k6 gold badges95 silver badges110 bronze badges
2 Comments
Phil
I guess it would, the only thing is that the table is huge (several million records) and Like will probably take a while. I was hoping there was some kind of native string functions in Mysql, but it looks like I'm stuck with Like...
Phil
Well, it worked, and didn't even take that long, so thanks :-)
You can use a LIKE condition with a leading wildcard. %.pdf means the column must end in .pdf
UPDATE mytable
SET newcolumn = 'PDF found'
WHERE yourcolumn LIKE '%.pdf'
Alternatively you could use the right() function
UPDATE mytable
SET newcolumn = 'PDF found'
WHERE right(yourcolumn,4) = '.pdf'
Comments
In your WHERE clause, use something like LOCATE('.pdf',url) > 0 to identify the record of interest.
As pointed out, this will give you any url that contains '.pdf' in the string...
If you definitely want the '.pdf' to be at the end, you can also try:
LOWER(RIGHT(url,4)) = '.pdf'
I would use LOWER or UPPER to deal with any case issues.
You can also use LIKE %.pdf (again, watch out for upper/lower case issues) as suggested
by others.
1 Comment
Rick James
If
url has a case-insensitive collation, you can forego the use of LOWER.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.
Hope this helps.
right()function.jpeg, you won't be able to use theright()method.