MySQL and regexp - return as a SELECT column with a table's column's regexp matching part only

Question

I know there is an easy way to give a WHERE condition matching a regexp in MySQL, but my question is different. The column is like this:

jknewfjnkewnjkfewnjfwe1jnkf2jnw wefwef 1234567.12345678 qwrqwerqwrq
jnewdnkewjk ewnfewf1 wefwefew2 1234568.22314152 qwrqwrqwr qw
whjefjwefwe1 wefwefwef2 qweqwrqrw 1234369.21213131 qwdqwdqwd

I would like to get a SELECT column SUBSTRING phrase, which returns for me:

selectcol1  selectcol2
1234567     12345678
1234568     22314152
1234369     21213131

All I know: first matching number is 7 digits, and 2nd matching number is 8 digits always, and the parts before and after surely won't match the exactly 7 digit pattern.

Is there any way to get these SELECT columns?

Rick James · Accepted Answer · 2015-03-31 21:36:42Z

0

Short answer: No. Long answer...

REGEXP '[[:<:]][0-9]{7}[[:>:]].*[[:<:]][0-9]{8}[[:>:]]'

will match the first 'word' of 7 digits followed (eventually) by a 'word' with 8 digits.

Demonstration (1=true):

mysql> SELECT 'jnewdnkewjk ewnfewf1 wefwefew2 1234568.22314152 qwrqwrqwr qw'
    ->        REGEXP '[[:<:]][0-9]{7}[[:>:]].*[[:<:]][0-9]{8}[[:>:]]' AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Keep in mind that SQL is not a good language for extracting those fields. In fact, I would say that you should use PHP (or other language) to extract the fields after MySQL locates and fetches the rows.

That is, there is no LOCATE() with regexp.

answered Mar 31, 2015 at 21:36

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dszakal Over a year ago

If it was used commonly, I'd use an algorithmical language anyway, but in this case it was just "runonce reporting laziness", so in this case performance wouldn't matter anyway. (I also know, storing MySQL columns like this is bad, but this database was not designed by me)

Collectives™ on Stack Overflow

MySQL and regexp - return as a SELECT column with a table's column's regexp matching part only

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related