[Solved] Help with regular expression and id_hierarchy from content db table

[nhaack]

Hi there,

within the plugin content_dump, I have a mysql query with a regular expression to find pages belonging to a content branch (e.g. page id 50).

Code: Select all

SELECT
   XYZ
FROM
  cms_content
WHERE
   XYZ
AND
   id_hierarchy REGEXP '50'

The problem, this also matches page IDs like 505, 150 or other hierarchy ID strings containing '50'

Of course, as long as I do not have as many pages or the other parameters prevent the plug-in from coming across any other page IDs containing '50', the problem will never arise. It's obvious now that I know it, but it never surfaced previously.

RegExp is not my best subject. I tried some things, but I just can not foprmulate what I have to do.

It needs to be like: if it contains '50' and '50' has a preceding '.' or nothing and is followed by a '.' or nothing. So it would match e.g. "50.60.70" or "60.50.70" or "60.70.50" but not "505.20.30" or "120.150.60".

I tried:

Code: Select all

[\.]*50[\.]*

But this matches if there is none or more '.' - so '550.412.120' would also match for '50'.

Using:

Code: Select all

[\.]+50[\.]+

Matched if there is at least one '.' - but this is not the case if the ID is in the first or last position.

I'm blocked at the moment. Does anyone have a hint what I could do? Any help is much appreciated.

Best
nhaack

[nhaack]

#BUMP#

No one with a hint... or is the solution too obvious ?

Best
Nils

[NaN]

try this (did not test it):

Code: Select all

(\d+\.)*50(\.\d+)*

This should match the following route:

A group of at least one digit followed by a dot. And this group as many as possible (none or more).
Followed by 50.
Followed by a group of  a dot followed by at least one digit. And again this group as many as possible (none or more).

So it matches e.g:

50
123.456.50.45.3
50.10
10.50

etc.

And it will also find

50.50.50

Hope that helps.

[JeremyBASS]

IIRC (.*?)(^50)(.*?) and $2 if you need it... Hope that helps

Cheers
Jeremy

[nhaack]

Thank you both for your feedback. I will give it a shot tonight and will let you know if I could fix the problem.

Thanks again & Best
Nils

[JeremyBASS]

wow.. that's odd... how did NaN get that post ahead of me ... very wired lol... Cheers

[nhaack]

I justed tried your expressions and they brought me to the solution (both where particularly good at finding specific cases but didn't work in all situation.

The pattern from Jeremy for example didn't match 20.30.50 or also matched 501.20.30
The pattern from NaN for example was catching e.g. 20.30.50 but also matched 501.20.30

I stumbled over a tool in the German Board: http://gskinner.com/RegExr/ it allows you to play around with the expression in real time. I finally found an expression that worked well so far.

Code: Select all

(^50(\.|$))|(\.50\.)|(\.50$)

It checks if the string either begins with a "50." or is just "50" with the first pattern-part (divided by "|" = or). Alternatively, it matches when there is the sub-string ".50." included. The last pattern-part used is to check whether the string ends with a ".50".

I will now go ahead and implement it in the plugin. Thanks guys.

Best
Nils

[calguy1000]

I would've used

( LIKE ('50.%') OR LIKE ('%.50.%') OR LIKE ('%.50') )

[NaN]

I would've used

( LIKE ('50.%') OR LIKE ('%.50.%') OR LIKE ('%.50') )

This is so simple i would have never thought about doing it like that.

[nhaack]

Hehe, indeed. Now as I see it... dough ... Thanks a lot Calguy.

Best
Nils