Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Oct 2008
    Posts
    214
    Thanks
    5
    Thanked 22 Times in 22 Posts

    MySQL REGEXP question

    Hello!

    I have a very poorly written database struct that I must query...

    The particular field that I'm interested contains "special" data. The data is as follow:

    maker=Foo&year=2001&somevalue=abc

    = and & are reserved characters so if they are present in a value they are escaped that way:

    maker=Test%3Dfoo%26bar&year=2001&somevalue=abc
    (%3D and %26)

    Every special field have an unique name. The special field order in the database field can vary. I know this isn't the best design but I have no control over it.

    Now I must get all the distinct values of a particular "field". I was thinking of using MySQL REGEXP, but I'm very bad with regular expressions.

    Which MySQL regular expression would retrieve the value part of a particular special field (like "year" in my previous example)? The special field can be at the beggining of the string or at the end. It must also work correctly with things like:
    someyear=2002&year=2001
    (only 2001 will be found here)

    Anyone can help with this regular expression? Also is there a better way than REGEXP to achieve what I want to?

    Many thanks!
    Last edited by AlexV; 03-18-2009 at 07:35 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    So far as I know, MySQL can't *extract* data from a field according to a regular expression; it can only use a regular expression in doing a match.

    Yeah, look here:
    http://dev.mysql.com/doc/refman/5.1/...perator_regexp

    So I think the best you can do is FIND those patterns in those fields and then use a regular expression in your implementation language (PHP, ASP, whatever) to then extract the needed value.

    That's the bad news. The good news is that matching is easier.

    So, for your "year=nnnn", for example, you could just use
    Code:
    SELECT ...
    FROM table
    WHERE field RLIKE '(^year|\\&year)\\=\\d{4,4}'
    Untested, but looks like it should work. Will only find "year" at the start of the field or after an & character.

  • #3
    Regular Coder
    Join Date
    Oct 2008
    Posts
    214
    Thanks
    5
    Thanked 22 Times in 22 Posts
    I managed to extract the data I wanted with :

    Code:
    SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('&', my_field), '&myvalue=', -1), '&', 1) AS 'myvalue'
    FROM mytable
    WHERE <some cond>

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    Ahhh...I misread your problem!

    You wrote "The special field order in the database field can vary," and I read that to mean that the actual field *name* varied, as well! So I kind of assumed you were going to need to be finding a set of possible field names.

    Yeah, your trick with substring makes eminent sense.

  • #5
    New to the CF scene
    Join Date
    Oct 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can someone help me out on a variation of the same problem?


    I have data in the form of "@abc blah blah blah" and "blah blah @xyz blah blah"

    How can I extract "abc" and "xyz" from query???


    Thanks for the help...

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    Again, in MySQL, you can only use a regexp in the WHERE clause. It's kind of useless in the SELECT.

    So you would have to use ordinary string manipulation in the SELECT.

    Unless it is direly important that this be done in the SQL query, I'd just use WHERE to find the records with content like this and then extact the actual strings in PHP/JSP/ASP/whatever you are using with MySQL.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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