I have two fields in the table (text) that receive several lines (paragraphs) of information, for example:
CAMPO1
linha1
linha2
linha4
linha5
CAMPO2
linha1
linha2
linha3
linha4
I need to check which line is missing by crossing the information of the two so that, looking at the example, I am able to indicate that linha3 is missing in CAMPO1 and missing linha5 in CAMPO2 .
Is there a solution for this query?
With a simple query this is not possible.
You could, however, implement a function or procedure to compare the texts, but I do not know and I did not find a text difference algorithm implemented for MySQL.
Anyway, doing this in the query does not pay. It would be better to retrieve the texts from the base and make use of some library of your language or tool available on the server to perform the comparison.
If you really need to retrieve this through a query, another strategy would be to rephrase the base so that you store each line of text in another table separately. So you could easily do a query like this:
SELECT LINHA
FROM TEXTO
WHERE TEXTO.ID = 'id-texto-1'
AND TEXTO.LINHA NOT IN (
SELECT LINHA
FROM TEXTO
WHERE TEXTO.ID = 'id-texto-2'
)
UNION ALL
SELECT LINHA
FROM TEXTO
WHERE TEXTO.ID = 'id-texto-2'
AND TEXTO.LINHA NOT IN (
SELECT LINHA
FROM TEXTO
WHERE TEXTO.ID = 'id-texto-1'
)
The disadvantage of this is that the queries do not consider the order of the lines, but if it is not a problem it should work.
I would still like to create a function that converts text to a temporary table if you do not want to write the lines separately, but this will make the query much slower and a bit tricky to implement.