CODEWITHSUNDEEP
mysqlregex
Fabrizio
Fabrizio

Reputation: 3776

MySQL REGEXP matching positive numbers

I have a column varchar[25] with this data inside :

the numbers might change in size from a 1 digit to a n digits. and I need to be able to pull any row that has at least one positive number in it

I was thinking that something like 

REGEXP '[^-,][0-9]+'

but this pulls -886 as 88 matches the regexp

Upvotes: 0

Views: 1333

Answers (5)

Arachnid
Arachnid

Reputation: 241

^\b\d+\b$ will give you positive integers.

^[^-]\d+((,([^-]\d+))?)+$ will give you only the lists where all the values are positive integers

For a list with any positive integer (but all valid integers negative or positive) I thinks this will check out: ^((-\d+,)?)+[^-]\d+((,([^-]\d+))?|(,-\d+)?)+$

Here's a great site for Regular Expressions: http://gskinner.com/RegExr/

I use it all the time for testing live.

Upvotes: 0

Fabrizio
Fabrizio

Reputation: 3776

I was able to figure out the best solution:

`COL` NOT LIKE  '%-%'

I forgot to mention that the column might also contain words like:

  • all,-886
  • none,886
  • 0,1,2,3,none
  • 0
  • etc...

Upvotes: 1

Etherpulse
Etherpulse

Reputation: 166

Try using this : 

"[^-\d]\d+\b"

which should work if i understood your question correctly.

a good regex reference table : http://www.regular-expressions.info/reference.html

Upvotes: 1

Marc B
Marc B

Reputation: 360592

Try

REGEXP '[[:<:]][^-,][0-9]+[[:>:]]'

The :<: and :>: portions indicate word boundaries.

Upvotes: 0

ajreal
ajreal

Reputation: 47321

you probably does not require regex

COL not like '-%' AND COL not like '%,-%'

however, this is the bad example of storing into incorrect data type,
split , and store into multiple rows ...and you can save some time for handling something like this question

Upvotes: 1

Related Questions