MySQL Database Tutorial – 15 – Regular Expressions




Facebook – https://www.facebook.com/TheNewBoston-464114846956315/ GitHub – https://github.com/buckyroberts Google+ …

Original source


42 responses to “MySQL Database Tutorial – 15 – Regular Expressions”

  1. In Oracle the fist example would be like:
    SELECT name FROM items WHERE REGEXP_LIKE(name, 'new');
    or
    SELECT name FROM items WHERE REGEXP_LIKE(name, 'New');
    considering that the Oracle Database is case sensitive, all the other examples works using this same structure.

  2. well , what if i want to search for REGEXP "car" in the Title column and in the Content column …?
    *EDIT:I JUST TRIED THIS AND IT WORKED*
    SELECT * FROM `articles` WHERE
    (author REGEXP 'Hello')
    OR (title REGEXP 'Hello')
    OR (content REGEXP 'Hello')"

  3. For LINUX user (maybe other else) , I found REGEXP works a little different here:
    Take this word 'bookkeeper' for example
    , you can't use [REGEXP 'bo{,2}k{,2}e{,2}per]  << (In linux REGEXP could do this.)
    , you have to use [REGEXP 'bo{0,2}k{0,2}e{0,2}per]
    to match the word.

  4. @ 4:49 , what if I wanted to return 1 boxes of frogs, 23 boxes of frogs and 45 boxes of frogs? …I understand it as allowing those chars from the ascii char index… so all these would be possible:

    1 box, 2, 3, 4, 5, 11, 12, 13, 14, 15, 21, 22, 23, 24, 25, …44, 45, 111… etc any number able to be made with those 5 numbers?

  5. You can do so by leaving spaces between numbers.

    SELECT name
    FROM items
    WHERE name REGEXP '[1 2 3 4 5 48] boxes of frogs'

    This will output:
    ————————
    name
    3 boxes of frogs
    48 boxes of frogs

  6. right. so if you need boxes that have more than 9 frogs you type:
    '[1-9][1-9] boxes of frogs'
    as this means everything from 10 – 99. regular expressions work only for single chars at a time so you cant seach for "10 to 29" but you can search for first number: [12], second number [0-9].

  7. You cannot do more than one integer, even with space and parenthesis. Trust me. The parenthesis are only supposed to represent one character, but feel free to combine them Everyone else should stop answering based off guesses, it is very misleading for people who are really trying to learn here.

  8. If people are still wondering about this , think about the set between the [] as a char array rather than an integer array . So like if you want to get an item like "48 boxes of frogs" , you could just do REGEXP '[0-9] boxes of frogs' , as only 8 is needed for it to be returned . A more practical example would be trying to do REGEXP '[abc….xyz]word' . In this case , it would get all the entries that start with any letter of the alphabet , + "word" (aword , bword , cword etc) .

Leave a Reply