How to search for data in MYSQL using Regular Expressions?

Asked

Viewed 31 times

0

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

To illustrate
In the user table, you have the "cards" field. The letters are inserted as below:

inserir a descrição da imagem aqui

In the above template there are 3 cards inserted, but in the system there are more than 80 cards. And the levels range from 1 to 20

Then I need to compare with another table, which will not have 80 cards but 3, as below:

inserir a descrição da imagem aqui

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

How to do this query using MYSQL?

mysql

  • Can I ask you a question, wouldn’t it be better to create relational tables by separating by "types of cards", "cards that people have" (this could include the level), "people"? I mean, that would make more sense the data relationship, using foreign keys, I can formulate a suggestion in the answer I’ve already made, I just need to know if this would suit you.

1 answer

1

You could experiment with LIKE even, since the intention seems to be to seek only by the number that comes before the LVL, thus:

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

Would only be 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 believe it would be so (I have not tested, if it fails let me know):

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

Explaining the regex:

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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.