I'm using a mysql database. The one table has a field of a website. All rows have something like "http://www.xyz.com/cgi-bin/foo.pl?bar=1" or something to that affect.
I want to remove all the "http://www.xyz.com/cgi-bin/foo.pl?bar=" so only the last number is left. I am sure I could write a php script to update the table like I want, but I want to know if there is a simple command in SQL to handle this problem. What's the easiest way to make this update?
(I'm not familiar with MySQL, but the principles remain the same and should be child's play to translate into MySQL syntax - consult that manual!)
In order to isolate the number on the right hand side of the equals sign you need to do the following:
1) Find the position of the equals sign in the string (I shall do the search from right to left, but it only matters if there are other parameters on the querystring)
2) Grab the part of the string after this point.
These two sub-stages can be easily incorporated into a single SQL "UPDATE" query - the following is an example in VBScript/JET SQL syntax (which works on MSAccess and SQL Server):
UPDATE TableName SET [FieldName] = Mid([FieldName],InStrRev([FieldName],"=")+1);
It works for me (knocked this up in MSAccess, and tested it on your test string)! As I said at the start, translating this into the appropriate syntax for MySQL should be pretty damn easy.
If you want to parameterize the "foo" and "bar" bits of the url then it will be a little more complicated, but still along these lines. If this is the case and you get stuck, let me know!
InStrRev returns the index of a given substring in a string, but backwards from the end. The forwards version is just plain InStr. These are VBScript commands, but I am sure that there must be a MySQL equivalent. It is a fundamental string manipulation function.
INSTR seems to be the same as VBScript's InStr, but there doesn't seem to be an equivalent for InStrRev.
It's probably best to stick to the forward-searching INSTR rather than concocting a cunning InStrRev replacement using a multiple REVERSEs...! It all depends on how many querystring parameters there are. If there's just one, then using plain ol' INSTR should be fine.
If not, then it might be worth looking into using a regular expression to grab the bit you want, but that may be overkill, and will open a whole new can of worms! Only pursue this if absolutely necessary!
Posting Permissions
Bookmarks