J
If they are the users themselves who put the phones in the comments, then there is not much control over the format.Of course you can consider some more common formats. By their examples, I saw that it can be the 9 digits of the mobile number, all together or separated by spaces (999999999, 9 9999 9999 or 99999 9999), being the optional DDD.A https://pt.stackoverflow.com/a/357752/112052 suggests a very complex regex to contemplate these (and many others) cases, but the https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-syntax Unfortunately https://www.regular-expressions.info/mysql.html and will not support all proposed resources, as https://www.regular-expressions.info/shorthand.html or https://stackoverflow.com/a/39727000 (excerpts starting with (?=, (?! and (?<!).Thus, it follows an alternative that checks some formats:select * from tabela where comentario REGEXP
'(^|[^0-9])(\\(?0?[0-9]{2}\\)?)?9 ?[0-9]{4} ?[0-9]{4}([^0-9]|$)';
(^|[^0-9]): | https://www.regular-expressions.info/alternation.html . Therefore, this passage means " https://www.regular-expressions.info/anchors.html "^) or "anything that is not number" (the [^ means https://www.regular-expressions.info/charclass.html#negated , that is, I don't want 0-9 - no digits from zero to 9).This ensures that so far I can be at the beginning of the string (it will be that the phone is already at the beginning), or it has any character that is not a number (avoiding you take cases like 3393333333333333333).Then we have (\\(?0?[0-9]{2}\\)?)?. We go through parts, from inside out:0?[0-9]{2} - an optional zero (0? - ? indicates " https://www.regular-expressions.info/repeat.html ", what is the same as saying "optional"), followed by 2 digits ([0-9] is any digit from 0 to 9, and {2} what to say " https://www.regular-expressions.info/repeat.html#limit "), because the DDD can be written as 11 or 011\\(? and \\)? - the parentheses can be optional. I did so because only the parentheses ( and ) have special meaning in regex, as they serve https://www.regular-expressions.info/brackets.html . That's why we have to escape them with \\.Finally, all this passage is in brackets (i.e. grouped into a single sub-expression), and ? in the end makes this whole stretch optional.That is, the DDD is optional.Then we have 9 ?, which is number 9 followed by an optional space (repare that there is a space before the ?, that is, the space that is optional, not the 9). Here I am assuming that it will be only cell numbers that start with 9 - remembering that in the future we will be able to have phones that start with 8, 7, etc, so it is at your discretion to always leave 9 or switch to [0-9] (or) [7-9] if you want to start only with 7, 8 or 9, etc.).Then we have [0-9]{4} (4 digits), followed by an optional space, plus 4 digits.And finally, we have ([^0-9]|$): any character other than number or the end of the string ($). This also ensures that you won't get more digits than necessary, preventing you from taking for example 3393333333333333333. http://sqlfiddle.com/#!9/9a4e837/8 you can see this query working.If you want to increment, you can place the separator as a hyphen or space, for example, such numbers would be accepted as 9 9123-4567 or 99123-4567. Just change the optional spaces per [ \\-]? (a space or a hyphen, optional). The regex would look like this:select * from tabela where comentario REGEXP
'(^|[^0-9])(\\(?0?[0-9]{2}\\)?)?9[ \\-]?[0-9]{4}[ \\-]?[0-9]{4}([^0-9]|$)';
http://sqlfiddle.com/#!9/85a631/2 it working.You can also add an optional space after DDD:(^|[^0-9])(\\(?0?[0-9]{2}\\)?)? ?9 ?[0-9]{4} ?[0-9]{4}([^0-9]|$)
^^
Without this space, the DDD is ignored for cases like (11) 9 9123 4567 - only the phone number is captured by regex, but the DDD does not, https://regex101.com/r/Qpi5bL/1 . Already placing the optional space, the DDD is also captured, https://regex101.com/r/Qpi5bL/2 .Another detail is that we are considering only mobile numbers. But there are still 8-digit phone numbers (in residences not so much, but in companies, it is still quite common). If you also want to consider these numbers, just put 9 as optional:(^|[^0-9])(\\(?0?[0-9]{2}\\)?)? ?9? ?[0-9]{4} ?[0-9]{4}([^0-9]|$)
^^
Just remembering that, as are the users who type their numbers, there may always be some strange format that you did not predict. And the more possibilities, the more complex the regex gets.For example, the regex I suggested only considers an optional blank space. But if you want me to have more than one space, you can change ? by * (zero or more occurrences), or limit the amount with keys (e.g. {0,3} limits between 0 to 3 occurrences).There is also the possibility of a CPF being confused with a phone, since both can be written without any separator (43912341222 can be both a CPF and a DDD + phone - even if people custom write the CPF as 439.123.412-22, who guarantees you won't have a case like that? Anyway, see if this applies to your cases). Anyway, regex isn't such a "magic" business, and it's up to you to assess whether you're going to trust her so much to the point of automatically removing anything she takes...Perhaps it is better to follow the suggestion given in https://pt.stackoverflow.com/a/357752/112052 and verify this before of inserting into the bank. I don't know what language you're using, but https://www.regular-expressions.info/tools.html , which allows writing expressions https://regex101.com/r/PN7pJk/1 , for example (using \b to delimit the phones, without having to use the "truck" I did above with (^|[^0-9]) and ([^0-9]|$), besides use \d as a shortcut to [0-9] and \s for spaces).
