0

I need to extract an customer number of 5 to 6 digits long from a comment column from a mysql table.

I found documentation to use WHERE REGEXP() for filtering some results with a regular expression but nothing to extract a part of a sentence in SELECT statement.

What I would is something like this :

SELECT SUBSTRING(`commment`, REGEXP('^[0-9]{5,6}$')) as `customer_number`

Example of content :

id | firstname | lastname | comment 
---------------------------------------------------------------------
 1 | John      | Doe      | Abo 674202 du 01.06.17-31.05.18/emu.
 2 | Jane      | Doe      | Abo. 96735 mis en service le 23.05.17/emu

I need to extract 674202, 96735, ...

Meloman
  • 3,558
  • 3
  • 41
  • 51
  • You can return desired rows and extract data from regex within your app. You can't directly do this in MySQL. You had a chance to do if you have been running MariaDB. – revo Mar 29 '18 at 10:36
  • I need to make an export in CSV directly from mySQL so no app ! – Meloman Mar 29 '18 at 10:50
  • Sorry for duplicate (if it really is) @dasblinkenlight but title of question already answered is not very speaking ! – Meloman Mar 29 '18 at 10:55
  • 1
    This question has accumulated lots of duplicates over the years, because many developers think that MySQL should support regex for data extraction, but it does not. All these questions end up with work-arounds at various levels of hacking, or installing a custom extension to MySQL (I would strongly recommend against it to avoid non-portability). [Here is the answer that says the feature is not supported](https://stackoverflow.com/a/5361524/335858). – Sergey Kalinichenko Mar 29 '18 at 11:02
  • mmmhh :( very bas news... in 2018 it's frustrating. Thanks anyway. I will need to make a macro in Excel from the export to extract it before to send CSV to my customer. – Meloman Mar 29 '18 at 11:05

0 Answers0