Select by varchar position

Question

I have in mysql database a field documento of the kind varchar.

Records follow the following pattern: 123456789,12X456789.
Or all in numbers or containing a letter in the third position.

How do I ride a select to capture only the records that have letters in the third position?

I tried how like but I had to assemble a select for each letter of the alphabet:

select * from tabela where documento like '11A%'
select * from tabela where documento like '11B%'
select * from tabela where documento like '11C%'

How I do it more efficiently?

Answer 1

You can filter with REGEXP in the document column, so Mysql will use the regular expression you specify to find its result. Here has a good tutorial on.

Example of the use of REGEXP in Mysql:

SELECT 
    documento
FROM
    tabela
WHERE
    documento REGEXP '^.{2}[a-z]'
ORDER BY documento;

Summarizing the regex roughly would be this:

• ^ indicates the beginning of the record
• . indicates any character
• {n} number of instances before the character you will specify
• [a-z] specifies that you want alphabet characters

Answer 2

SELECT * FROM test
WHERE
  test.id RLIKE '^[0-9]{2}.[0-9]*$'

To find matches between the values of a column and a specified regular expression use the operator RLIKE.

In the case the pattern specified in the question is:

• All elements are numbers.
• The third element can be a number or letter.
• In the question it was not specified the length of this value the left variable, and the author of the question should make the adjustment or ask for guidance in the comments.

In the documentation Mysql::Matching patterns is specified:

. corresponds to any unique character.

A class of character [...] corresponds to any character between square brackets. [a-z] corresponds to any letter, while [0-9] corresponds to any digit.

* is zero or more instances than the above.

use ^and $to match the beginning and the end.

use the operator {n} to repeat the pattern n times.

So reading the pattern '^[0-9]{2}.[0-9]*$' textually the pattern would mean:

Starting with two numeric digits, the third character is letter or numerical digit ending with zero or more numerical digits.

Example:

CREATE TABLE test (
  id varchar(20)
);
INSERT INTO test (id) VALUES ("123456789");
INSERT INTO test (id) VALUES ("12bb00233");
INSERT INTO test (id) VALUES ("12X456789");
INSERT INTO test (id) VALUES ("120c98755");

SELECT * FROM test
WHERE
  test.id RLIKE '^[0-9]{2}.[0-9]*$'

Upshot:

id
---------
123456789
12X456789

Testable example in DB Fiddle:

Answer 3

Are records set in numbers or is it a string? If only the records you search for will be Strings, you can try Convert the value into an INT and take only the values that have not been converted.

SELECT CAST('documento' AS UNSIGNED) FROM 'TABELA'

If it cannot convert, you know that the value is a string and you will get this value in specific.