Finding a number within text string using REGEXP?

Databases
1

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.

Thanks

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

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.

Thanks

4

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