![]() However, I imagine performance could be an issue with bigger databases, larger fields, greater record counts, and more complex filters.Īs always, use logic above as it makes sense. Regarding performance, I ran some minor tests against an existing table and found no differences between my variations. However, you can also mix these types of statements as you can see in the second example listed. This allows you to add specific matches with wildcard matches in the same expression. In the first example above, I use ^9999$ to indicate exact match. However, these expressions did not appear to yield the desired results. I experimented with using negating patterns, forward looking patterns, and so on. Notice I separated the NOT set in a separate WHERE filter. OR Mixed Alternative: SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 ' Use REGEXP Alternative: SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 |^9999$' You'll need to test your patterns and see what works.įinally, To Accomplish Multiple LIKE and NOT LIKE filters: SELECT * FROM fiberbox WHERE field LIKE '%1740 %' NOTE: Not all regex patterns appear to work in MySQL statements. There are more efficient ways to narrow down specific matches, but that requires more review of Regular Expressions. Inside () with * (.*) adds a repeating pattern indicating any number ![]() indicates any single character, except line breaks. Placing (.*) behaves much like the % wildcard. Placing $ after the value indicates end of line. Placing ^ in front of the value indicates start of the line. Use: SELECT * FROM fiberbox WHERE field REGEXP '^1740 |1938 $|1940 (.*) test' To Accomplish LIKE with Controlled Wildcard Placement: SELECT * FROM fiberbox WHERE field LIKE '1740 %' If you need more control over placement of the wildcard, use some of these variants: Typically, REGEXP will require wildcard expressions such as (.*)1740 (.*) to work as %1740 %. Values within REGEXP quotes and between the | (OR) operator are treated as wildcards. Use REGEXP Alternative: SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 |1940 ' To Accomplish multiple LIKE filters with Wildcards: SELECT * FROM fiberbox WHERE field LIKE '%1740 %' To add to this, here are some things I observed for those interested in using REGEXP: Paul Dixon's answer worked brilliantly for me.
0 Comments
Leave a Reply. |