CODEWITHSUNDEEP
mysqlregex
Dragos Custura
Dragos Custura

Reputation: 131

mysql regexp have a digit but not start with negative

i have rows with values like -4,3,2,1 and 3,2,1 or 3 or 2

i need to select any row that have value 2 including the row with value 3,2,1 but not the one that have - at begining.

thank guys

Upvotes: 0

Views: 127

Answers (3)

Dragos Custura
Dragos Custura

Reputation: 131

the best solution and the simplest that i could find:

where sponsored regexp '^[^-]*[1-2]'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I would be inclined to do this with like:

select *
from t
where concat(',', col, ',') like '%,2,%' and
      col not like '-%'

If you are looking for "2", but not delimited by commas, then:

select *
from t
where instr(col, '2') > 0 and col not like '-%'

To get a column that does not start with a minus sign and constains a "2":

where col regexp '^[^-]*2*'

If the commas are important, the following should work:

where concat(',', col, ',') regexp ',^[^-]*,2,*'

Upvotes: 1

Marc B
Marc B

Reputation: 360592

Why a regex?

SELECT ..
WHERE LEFT(yourfield, 1) <> '-'
  AND (
    (yourfield = 2) OR
    (yourfield LIKE '2,%') OR
    (yourfield LIKE '%,2,%') OR
    (yourfield LIKE '%,2')
 )

and then while you're trying to figure out why the WHERE is so complicated, you should go read up about database normalization

Upvotes: 1

Related Questions