How do I search for data in MYSQL using Regular Expressions?

Question

How do I search for data in MYSQL using Regular Expressions?

Navigation

#1 by (1 votes)

0

It is necessary to compare data from the MYSQL database, but the data to compare is not extremely similar. The MYSQL LIKE does not work in this case because there are some variables in that query.

To exemplify
In the user table, has the "letters" field. The cards are inserted as follows:

Intheabovemodelthereare3cardsinserted,butinthesystemtherearemorethan80cards.Andlevelsrangefrom1to20

Ineedtocomparewithanothertable,whichwillnothave80cardsbut3,asbelow:

You should display which cards have a level less than or equal to the registered. In case the cards of id 1201 and 1202 would pass.

How to do this query using MYSQL?

mysql

asked by anonymous 16.08.2018 / 03:37

1 answer

Compare two tables in mysql by grouping the main table and listing the result of the second table Hide div when clicking on it

score 1 · Answer 1

You could experiment with LIKE same, since the intention seems to be to look only for the number that comes before LVL , like this:

WHERE ('cartas' LIKE '(1201LVL%;' OR 'cartas' LIKE ';1201LVL%') AND ... <outras condições>

It would be only 2 likes , one for ; , ie if the id is in the middle and another if it is in the beginning with (

Now with regex I think it would be like this (I did not test, if it fails, let me know):

WHERE 'cartas' REGEXP '(\(|;)1201LVL[:digit:]+(\)|;)' AND ... <outras condições>

Explaining the regex:

(\(|;) must start with ( or ;
% with% 1201 is the dynamic value, which you should change and the 1201LVL common part of the field value
LVL looks for any number that comes after LVL, such as LVL1, LVL2, LVL3, etc.
[:digit:]+ should end with (\)|;) or ) (although in the end this is somewhat redundant since the former the previous parts of the regex would already solve)