-1

I need: a regexp for MySQL (php, PDO) usage. Regexp should find all numbers between brackets [ ], except number 150.

So I would like to get:

[3]
[25464]
[510]

But I would like to exclude: 

[150]

What I got:

\[{1}((?!150)[0-9])+\]{1}

and it works fine for newest version of MySQL, but I need something that would work also on an older version (probably 5.1).

Problem: Currently I get an error: 

1139 - Got error 'repetition-operator operand invalid' from regexp

I know I can't use ?. How can I replace it?

Additional info (edit): I'm redesigning the database and that's why I need to write this

II edit - why I need this: I need to retrieve all rows which in column "content" contains only one specified [150]. One column 'content' can contain zero [nr] or one specific [nr] or many different [nrs]. 

WHERE content REGEXP '\[{1}((?!150)[0-9])+\]{1}' = 0 AND content LIKE '%[150]%'
suz
  • 737
  • 2
  • 9
  • 22
  • You need to redesign your database. – Your Common Sense Feb 16 '16 at 09:21
  • You [can't use a lookahead](http://stackoverflow.com/questions/18317183/1139-got-error-repetition-operator-operand-invalid-from-regexp). Try [`\[([0-9]{1,2}|[02-9][0-9]{2}|[0-9][0-46-9][0-9]|[0-9]{2}[1-9]|[0-9]{4,})]`](https://regex101.com/r/cH6jS9/1) – Wiktor Stribiżew Feb 16 '16 at 09:21
  • @YourCommonSense Yep, and that's why I'm writing this regexp. – suz Feb 16 '16 at 09:22
  • Can you show us the actual _records_ which contain these numbers? Perhaps you can get away without using `REGEXP()`. – Tim Biegeleisen Feb 16 '16 at 09:25
  • @TimBiegeleisen I use this regexp to search within text (text column in db). This column contains text from blog, with html tags etc. Is this information sufficient for you? – suz Feb 16 '16 at 09:31
  • What I'm thinking is maybe you could use clever string manipulation to get what you need without resorting to an ugly regex. – Tim Biegeleisen Feb 16 '16 at 09:39
  • @WiktorStribiżew I saw this and it doesn't contain answer to my problem. – suz Feb 16 '16 at 09:39
  • @TimBiegeleisen I need to find all records which contain in column 'content' at least one [number]. If nr [150] exists in this column even once, I would like to exclude this record. Column 'content' can have many numbers between brackets (in one record), or not have them at all. I change nr 150 in a loop (it's flexible). I'm not sure if I can skip regexp usage but I'm open to any proposal :) – suz Feb 16 '16 at 09:44
  • MySQL is not well-suited to handle this problem. As others have said, you should either redesign your database, or do the heavy lifting in your app PHP code. – Tim Biegeleisen Feb 16 '16 at 09:45
  • I've already wrote it: **I'm redesigning the database and that's why I need to write this.** – suz Feb 16 '16 at 09:47
  • Minus 1 without a feedback = -10 for the author. It's clearly written question, as far as I know it's not a duplicate. – suz Feb 16 '16 at 09:56
  • @suz I think the downvote comes because you haven't clearly posted a set of content rows (the actual values) that accurately articulates the issue, nor an expected output for that input. I would like to help resolve this old abandoned question. I have a potential non-regex solution but I want to be sure I understand your question. Can you update your question? and then ping me? – mickmackusa Jun 01 '17 at 04:16

2 Answers2

1

Try this one, Hope this works.

 $string='[3]
 [25464]
 [510]
 [150]
 [100]';
 preg_match_all('/\[(?!150)\d+\]/', $string,$matches);
 print_r($matches);

This will match all digits(except 150) with brackets.
$matches[0] will contain desired result...

Sahil Gulati
  • 15,028
  • 4
  • 24
  • 42
  • Thank you for your contribution but I need something which I can use inside a query. – suz Feb 16 '16 at 09:35
0
WHERE     x REGEXP '\[[[:digit:]]+\]'
  AND NOT x REGEXP '\[150\]'

However, that will reject aaa[123]bbb[150]ccc. Should it be rejected? (Please give some sample data that be matched / rejected. Your goal is not crystal clear.)

Since LIKE is faster than REGEXP, this will be a little faster:

WHERE x REGEXP '\[[[:digit:]]+\]'
  AND x NOT LIKE '%[150]%'
Rick James
  • 135,179
  • 13
  • 127
  • 222