Finding a number within text string using REGEXP?

Using MySQL 5.1.41 on WinXP.

I have a table I use to store diary entries.

Stored within the field which holds the diary content (“post_content”, longtext datatype) are strings which count various things, e.g.

D1190 XYZ … and then I did something …
D1191 XYZ … and then I did something else …
D1192 XYZ … and then I did something else again…
And then something happened D1193 XYZ

What I’d like to be able to do is to extract a list of all the lines from the “diary_all” table where post_content is LIKE D%<number> XYZ%

I think I’d need to use REGEXP but I’m not sure of the regex syntax to search for a number inside a string.

Any advice much appreciated.


WHERE post_content REGEXP 'D[0-9]+ XYZ'

Thanks for your reply.

I can now get at the relevant rows via:

SELECT post_content
FROM j_diary_all ff
WHERE post_content REGEXP 'D[0-9]+ IBS';

I’d really like to be able to isolate the D[number] IBS fragment from the post_content field.

Is it possible to do so using a combination of INSTR() and SUBSTR()?

I could do it at work as we use Oracle, but it seems the instr() function works differently with MySQL and I can’t work it out.

For example, using Oracle, I could do this:

SELECT SUBSTR(‘D2 IBS, more stuff happened’,0,6) FROM DUAL;

And get an output of “D2 IBS”

But if I do the same with MySQL, I get no output.

I guess there is a similar function in MySQL, but I can’t find it.

I realise that my example above is simplistic - as the number after the D can range from 1 to 4 digits long, and the “D[number] IBS” bit can appear at the start of the “post_content” field, or in the middle / at the end.

Again, I’d use instr() in Oracle to isolate the bits I’m looking for, but without knowing the alternative for substr() I’m stuck.

Any advice much appreciated.


the mysql substring function is called SUBSTR or SUBSTRING, but i wouldn’t try to find your substring with sql

once you’ve used the regexp to filter the rows, just return the column to your application language and extract the substring there