SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: change data

Hybrid View

  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    change data

    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?

  2. #2
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    (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!


    M@rco

  3. #3
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql docs

    I looked up InStrRev in the MySQL manual and it doesnt use that code. My guess is that that is a Microsoft thing.

  4. #4
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


    M@rco

  5. #5
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had a look here:
    http://www.mysql.com/doc/S/t/String_functions.html

    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!


    M@rco


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •