0

I need a regular expression in POSIX ERE format (for MySQL) to match any single standing character (between 2 spaces) in a string :

"abc d e f ghi" should match to "d e f"

I have only very basic knowledge of regular expressions (in PHP) and can't get this to work in MySQL...

UPDATE: Please see my other question for details of what I'm trying to do and the function I'm using :

MySQL: how to remove all single characters from a string?

Community
  • 1
  • 1
Dylan
  • 9,129
  • 20
  • 96
  • 153
  • What should `abc d e f ghi j k l mno` match to? `d e f` or `j k l` or `d e f ghi j k l`? Are you sure you don't mean the original matches `d`, `e`, and `f`? – Eric Aug 05 '11 at 21:50
  • What I'm trying to do is to remove the single characters from the string... I found a function that removes the characters that match the pattern, but now I need a good pattern that matches the single characters.... – Dylan Aug 05 '11 at 21:57
  • i dont understand your question. do you need to run a mysql query using regexp operator, returning the all the records matching a single standing char? or something else? mysql regexp engine is for matching only (and matched groups are not captured) – guido Aug 05 '11 at 21:58

3 Answers3

1

Does " [a-z] " do what you want?

Eric
  • 95,302
  • 53
  • 242
  • 374
  • Not entirely, it doesn't seem to work when 2 single characters are behind each other ? – Dylan Aug 05 '11 at 21:55
  • @Dylan: It will work if you replace every space with two spaces first! – Eric Aug 05 '11 at 22:00
  • That's a good suggestion... I have it working in Regexbuddy now, but sadly the function I found for MySQL doesn't seem to work with this pattern... – Dylan Aug 05 '11 at 22:04
  • How about "[^a-z][a-z][^a-z]" which means not a letter, a letter, not a letter? I can't test it here. – BF4 Aug 05 '11 at 22:09
  • Again, it's working in Regexbuddy, but not in my function....I guess that function has some problems... – Dylan Aug 05 '11 at 22:17
  • The problem is that after matching the first one, you claim the space from the second one. – Eric Aug 05 '11 at 22:21
  • Agree with @Eric. However, does it matter? This should match at least one standing character in the string, and would therefore match the whole string. The question didn't say anything about capturing did it? If not, this should work. It will match the line/string if that is all that is needed. – djhaskin987 Nov 02 '12 at 02:13
0

You should use MySQL word boundaries:

  • [[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; -> 1 mysql>

So that your MySQL regex would be [[:<:]][[:alpha:]][[:>:]]. This will match any standing alphabetic character in MySQL, as the asker seems to actually need. however, if it simply a non-space character surrounded by blank space, the RegEx [[:<:]][^[:blank:]][[:>:]] should be used.

Note that this word boundary syntax is not in the POSIX ERE Standard, but many other tools have similiar syntax; for example, GNU grep takes '\<' and '\>' in their REs to mean left- and right- word boundaries.

djhaskin987
  • 9,741
  • 4
  • 50
  • 86
0

general posix way: / ([a-z]) /ig but if its MySQL RLIKE, it would be ' [a-z] ' (as for case it depends on column collation

Jacek Kaniuk
  • 5,229
  • 26
  • 28