Select field value by regex?

Databases
1

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:

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

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

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

Hi Mittineague,

I tried the link and tried something like this:

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!