SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    I Never Give Up roosevelt's Avatar
    Join Date
    May 2005
    Posts
    515
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Select field value by regex?

    Hi,

    I am trying to extract some data from the database and I was wondering if MySQL can do this, if so, how?

    Here's a sample table

    id file_name
    1 b2000
    2 b3000
    3 b4000
    4 b5000
    5 b5000

    I am trying to do something like this:

    Code:
    SELECT DISTINCT REGEX '[0-9]{4}' file_name as serial_number FROM table;
    Expected Results

    serial_number
    2000
    3000
    4000
    5000

    But I don't think MySQL has this capability. Is there any way to achieve what I am trying to do?

    I am trying to avoid getting the rows and use PHP/Python to run the regex check, I believe if MySQL does the regex part it will be more efficient.

  2. #2
    I Never Give Up roosevelt's Avatar
    Join Date
    May 2005
    Posts
    515
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I ended up using the substring_index function and used 'b' as the delimiter. But I still would like a regex solution... in this particular scenario the substring_index worked fine but regex would give you more flexibility.

  3. #3
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,190
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    I've never used it in any of my queries, but the examples at the bottom look like they might be helpful.
    http://dev.mysql.com/doc/refman/5.1/en/regexp.html

  4. #4
    I Never Give Up roosevelt's Avatar
    Join Date
    May 2005
    Posts
    515
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi Mittineague,

    I tried the link and tried something like this:

    Code:
    SELECT file_name REGEXP 'b[0-9]{5}' AS serial_number FROM mytable
    The query works fine but the issue is it is only checking if the regular expression is valid or not. So, it returns 1 or 0 for the serial_number field. I am trying to extract the actual numbers using regex.

    Given the amount of years MySQL has been in development... and the popularity of Regex... not sure why no one thought about extracting column data using Regex o.O!


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
  •