Can I RegEx out the data I want from a field

... straight from the MySQL Query?

[trillianjedi]

Me again... ;)

SELECT stuff FROM table WHERE foo REGEXP "bar"

.... produces this for me:-

bar:trillianjedi@192.168.192.140;uniq=61F8A8B.......

That's an excellent start, but what I'm really digging for here is that IP address : 192.168.192.140

Can I RegEx that bit out straight inside this query, or am I going to have to loop the resulting dataset and then reduce it down to the IP address programmatically?

Thanks!

TJ

[txbakers]

In looking through the mySQL docs, the REGEXP command is way powerful, and it seems you can just pull out the digits.

Take a stroll over to the mySQL online documentation and read up on it. It was too long to copy/paste here.

REGEXP always gives me a headache when I try to formulate them.

[trillianjedi]

Thanks Tx - but I can't seem to find it in the manual (I had looked pretty extensively for it).

Can you link me to the page you looked at?

All I could find was RegEx-ing for word match. We might be at cross purposes here - what I need is to RegEx the result not the search.

EG, instead of getting result (which is one field):-

bar:trillianjedi@192.168.192.140;uniq=61F8A8B.......

.... I want to get handed:-

192.168.192.140

I'll have another look through the manual....

TJ

[zCat]

Here's some docu:

[dev.mysql.com...]

HTH

[trillianjedi]

Hey zCat - yes, that's the page I read.

It doesn't help - I'll assume I'm trying to do the impossible (it usually turns out that way ;)).

I'll do it the long way around...

Thanks anyway,

TJ

[zCat]

But I see what you mean: there doesn't seem to be a way of extracting the matched pattern. Hmm.

[zCat]

Aha, we replied at the same time. It's a question which interests me (I like regexes, they're my personal anti-Alzheimer training ;-), but I can't find anything in the MySQL docu for actually extracting the patterns.

(FWIW you can in PostgreSQL, which is one of the many reasons why I prefer it to MySQL).

[carguy84]

You just need the regular expression to extract out the IP address?

.*@(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}).*

Using parenthesis in Regular Expressions will group all the matches "together".

Chip-