MySQL Regular Expressions
I have used the Ad-Hoc report to look for booster+ but it has ignored the + and listed all the records containing 'booster'.
Why?The Ad-Hoc report takes advantage of what is called a 'Regular Expression'. By using these it is possible to narrow down the search using specific characters as we will show later. The plus symbol (+) is one such character.
To save you looking at the rest of this guide, if you are wanting to use
any of the following characters: +$.*?|(){}[]^ in the text your are looking for you will get weird results.
You need to 'Escape' these characters, that is, tell the system NOT to treat
them as special characters - you do this by prefixing them with two back
slashes (\) e.g. to look for booster+ enter booster\\+
What are these special characters used for?
| Char | Use | Example |
|---|---|---|
| ^ | Beginning of line | ^Booster Match only if booster occurs at the beginning of the line.
|
| $ | End of line | Booster$ Match only if booster is the last thing on the line.
|
| * | Multiple Characters | Match a sequence of zero or more characters. bo*ster = Bster, Boster, Booster, Boooster |
| + | Single Character | Match a sequence of one or more characters. As aboved except won't match bster. |
| ? | Individual Character | Match zero or one character. As * except only match Boster and Bster |
| . | Any Character | .at Match Bat, Cat, Eat etc.
|
| | | Or | felv|pentofel Match if either FELV or Pentofel appears on the line.
|
| () | Sequence Match | (abc)* Match Zero or more instances of the sequence abc |
| [] | Group | e.g. [0-9] match any number, [a-d] match abcd |
| {} | N/A | Alternative way of expressing a*, a+, a? |
So comming back to the earlier problem, looking for booster+ was translated to look for 'booste' followed by one or more 'r's - so yes it would return just booster.