www.webdeveloper.com
Results 1 to 7 of 7

Thread: Need advice on regex in mysql been searching the forum and online but not finding it

  1. #1
    Join Date
    Sep 2006
    Location
    Wixom, MI
    Posts
    292

    Need advice on regex in mysql been searching the forum and online but not finding it

    I have a mysql query generated in php that selects some products from a database table.

    What I need it to do is check the model_names field for a regular expression.

    Basically I wish to select 21 products from the product table but only ones that contain alphanumeric characters, standard keyboard symbols such as ! @ # $ % ^ & * ( ) _ - + = : ; " '> < . , ? /| \ } { [ ] space etc.

    My question is how would I format the regex to include the alphanumeric characters and my selection of symbols that I can add or remove as needed and an unlimited quantity of each in any order within the field, and not select rows where that field contains anything else.

    Currently I have
    Code:
    "SELECT * FROM `cms_product` WHERE `enddate` > '$timenow' AND `product_image` != '' AND `product_description` NOT LIKE '%http://%' AND `model_names` REGEXP '^[A-Za-z0-9-]+$' $useroptions ORDER BY RAND() LIMIT 21"
    Which is fine for selecting only alphanumerc characters for that field but I need to add a few symbols to the list and various punctuation etc.

    Please any help appreciated, trying to figure this out by tonight for an urgent change i need to make for a client but haven't used this before.

  2. #2
    Join Date
    Sep 2006
    Location
    Wixom, MI
    Posts
    292
    Ok to add i got this working

    Code:
    SELECT *
    FROM `cms_product`
    WHERE `product_image` != ''
    AND `product_description` NOT LIKE '%http://%'
    AND `model_names` REGEXP '^[A-Za-z0-9\$\.\,\;\:\!\#\%\&\*\@\+\=]+$'
    ORDER BY RAND( )
    LIMIT 21
    But it doesn't allow for the following ( ) { } [ ] (which may be in the model_names which when i try and add it breaks the whole thing and no results are found. I tried backslashing them \(\) and so on, no luck, tried double \\ no luck so how?

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    Not sure why you can't use those characters as long as they're escaped.

    From the big picture view: what is NOT allowed? It seems like a pretty inclusive list of characters now, and I'm wondering if limiting a few edge cases (if, in fact, it would be just a few edge cases?) is worth the query time? Of course, the fact that you have a NOT LIKE with a beginning "%" means you're already going to have to do a full table scan, and then you're doing an ORDER BY RAND, so at that point, maybe a bit more processing time doesn't matter?

    PS: Maybe you just want to screen out white-space or a few other specific special characters?
    Code:
    '^[^ \t\|]+$' -- not space, tab, or vertical bar
    Last edited by NogDog; 09-11-2013 at 04:59 PM.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Sep 2006
    Location
    Wixom, MI
    Posts
    292
    The problem is there is some other functionality later down the line that is getting messed up when records are chosen that contain various foreign characters among other things (and there are usually lots of them and sometimes messed up ones too, not my site so don't have time to rebuild it all to stop foreign characters or make them work lol), so I figured limiting it to only selecting records who's title contain nothing but a few basic punctuation and symbols that I know dont mess anything up, along with the standard alphanumeric characters and spaces etc was the best way to go.

    This script is run once a week to generate something so time is not a factor and if it takes longer to process no worries.

    I tried adding \( for an allowed character but it didnt escape the ( and it treated it as an open ( without a close ) and through an error, same for trying to escape ) { } [ ] so not sure what I am doing wrong.
    Last edited by BWWebDesigns; 09-11-2013 at 05:14 PM.

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    Some thoughts:

    If this is in a PHP script and the query is within double quotes, you may need to triple escape them (essentially escaping the backslash itself).

    You could use character names, e.g.:
    Code:
    '^[a-zA-Z[.left-parenthesis.][.right-parenthesis.]]+$'
    See this page for more info (scroll down a bit): http://dev.mysql.com/doc/refman/5.1/en/regexp.html

    Also, you might be able to use character ranges (hyphen-separated) that happen to include those characters, perhaps.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Sep 2006
    Location
    Wixom, MI
    Posts
    292
    Thanks the php thing isn't going to help as I tried it in the phpadmin itself and it wasnt working, but the name suggestion worked a treat, now the ones you suggested which of these do they represent () {} [] and what would the others be if you could tell me that would be great, if not no worries I will try and track the info down online

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    They're all listed on the page I linked to above. (Search for "[.characters.]", which is the section heading.)

    At a quick scan, looks like the others are "[.left-square-bracket.]" and "[.left-brace.]" (or "[.left-curly-brace.]") with their corresponding "right-" versions. (Double-check, though, as I just typed those from memory after looking at the page again. )
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles