Difference between texts with multiple paragraphs in Mysql

Question

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 cross-referencing the information of the two so that, looking at the example, I can indicate that the linha3 in the CAMPO1 and which lacks the linha5 in the CAMPO2.

Has solution for this query?

Answer 1

With a simple query this is not possible.

You could, however, implement a function or procedure to compare the texts, but do not know and did not find a text difference algorithm implemented for Mysql.

Anyway, doing it on the consult doesn’t pay. It would be better to recover the base texts 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 reshape the base to store each line of text in another table separately. Then 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 should work.

It would even give to create a function that converts the text into a temporary table if you do not want to save the lines separately, but this will make the query much slower and is a little complicated to implement.