16

Does anyone know how to match even numbers and odd numbers of letter using regexp in mysql? i need to match like a even number of A's followed by an odd number of G's and then at least one TC? For example: acgtccAAAAGGGTCatg would match up. It's something for dna sequencing

thunderb0lt
  • 163
  • 1
  • 1
  • 4

2 Answers2

36

An even number of A's can be expressed as (AA)+ (one or more instance of AA; so it'll match AA, AAAA, AAAAAA...). An odd number of Gs can be expressed as G(GG)* (one G followed by zero or more instances of GG, so that'll match G, GGG, GGGGG...).

Put that together and you've got:

/(AA)+G(GG)*TC/

However, since regex engines will try to match as much as possible, this expression will actually match a substring of AAAGGGTC (ie. AAGGGTC)! In order to prevent that, you could use a negative lookbehind to ensure that the character before the first A isn't another A:

/(?<!A)(AA)+G(GG)*TC/

...except that MySQL doesn't support lookarounds in their regexes.

What you can do instead is specify that the pattern either starts at the beginning of the string (anchored by ^), or is preceded by a character that's not A:

/(^|[^A])(AA)+G(GG)*TC/

But note that with this pattern an extra character will be captured if the pattern isn't found at the start of the string so you'll have to chop of the first character if it's not an A.

Daniel Vandersluis
  • 91,582
  • 23
  • 169
  • 153
1

You can maybe try something like (AA)*(GG)*GTC

I think that would do the trick. Don't know if there's a special syntax for mysql though

Johanna
  • 223
  • 2
  • 4
  • 9