Mysql: How to find string from a given character?

Question

I’m developing a small system - and I’m not an expert on Mysql (so the doubt may be silly)

I receive from the client a giant string with 580 characters - this string contains a series of information such as name, date of birth, address... etc..

Store string in mysql.

However, to locate each record I need to know the person’s number.

CPF is located from character number 12 to 22 - that is - something like this:

1234567890A27485962812CLOVIS DA SILVA

where CPF = 27485962812

How to locate ALL bank records that match this CPF?

I thought about using "LIKE" but I’m afraid that - you know it - the same sequence is the same in another part of the string giving a false result.

SELECT * FROM mytable WHERE CAMPO1 LIKE '%CPF%'

At first I thought about dismembering all the data and putting each information in a column of the database, but I was worried about "dismembering" something wrong - and I don’t know if doing so would be the most recommended practice - Maybe leaving all the hints in a single column as I’m getting is faster to process later.

something else - using that same reasoning - is it possible to filter the records by a date? This because in the middle of the string I also have the "expiration date" of the information.

so I would only display at the end, the records still "valid" (that’s the least pq once having all the CPF records I know how to make a looping and delete the "invalid" records - but I think it would be cool and more elegant - if the query itself already brought the right information)

In case the EXPIRY DATE would be located between the character 300 and 309 (DD/MM/YYYY) = 10 characters.

any idea?

thank you so much for the time devoted to helping me :D

Daniel

Answer 1

You can use the SUBSTRING function:

SELECT * FROM mytable WHERE SUBSTRING(CAMPO1 ,12,11) = 'cpf';
                              

Explaining the function -> SUBSTRING(Coluna, a partir deste carcter, quantidade de caracteres).

In relation to date, the same logic applies, puts an AND substring location of the date in the string and makes the logic with the value.