16

I have two SQL files, one is old.sql and the other one is new.sql.

Suppose old.sql contains a table with three fields, Emp_Id, Name and Address and data stored in old.sql as follows:

Insert into table1 values (101 ,"a", "xyz");
Insert into table1 values (102 ,"b", "pqr");

Then I have changed "a" address "xyz" to "xyz123" and saved that data in the new.sql file. Now the new.sql file contains data as follows:

Insert into table1 values (101 ,"a", "xyz123");
Insert into table1 values (102 ,"b", "pqr");

When I use the diff command like this:

diff old.sql new.sql

it gives differences line-wise but I want only updated data, like xyz123.

5 Answers 5

17

Short answers from here:

git diff --word-diff=color --word-diff-regex=. file1 file2

And here:

diff -u file1 file2 |perl /usr/share/doc/git/contrib/diff-highlight/diff-highlight

2
  • The first command is a true character-by-character comparison Commented Dec 16, 2020 at 3:15
  • 2
    A small tip for the git example above: Add --no-index and you can diff untracked files as well. Commented Jan 23, 2023 at 9:47
9

You might find wdiff useful for this type of comparison; it's a front-end to diff which produces word-by-word comparisons. With your example it produces by default

Insert into table1 values (101 ,"a", [-"xyz");-] {+"xyz123");+}
Insert into table1 values (102 ,"b", "pqr");

It can use terminal features to make the output more legible on a terminal (wdiff -t). It also has a -3 option which limits output to changed words only:

======================================================================
 [-"xyz");-] {+"xyz123");+}
======================================================================

If you don't have wdiff already installed, you need to install it. Run sudo apt-get install wdiff or sudo dnf install wdiff or sudo yum install wdiff or the command that is appropriate to your operating system.

4
  • 1
    but when I use wdiff command instead of diff command prompt is showing error wdiff command not found
    – user168519
    Commented Jun 2, 2016 at 12:16
  • 1
    @user168519 that simply means you need to install it, sudo apt-get install wdiff or sudo dnf install wdiff or sudo yum install wdiff or your local equivalent. Commented Jun 2, 2016 at 12:21
  • Hi Stephen, I have installed wdiff but it is not comparing data present inside the parenthesis.
    – user168519
    Commented Jun 2, 2016 at 13:35
  • @user168519 that's odd, could you edit your question to show wdiff's behaviour? Commented Jun 2, 2016 at 13:58
5

You can use:

diff -u old.sql new.sql |colordiff |diff-highlight

screenshot

colordiff is a Ubuntu package. You can install it using sudo apt-get install colordiff.

diff-hight is from git (since version 2.9). It is located in /usr/share/doc/git/contrib/diff-highlight/diff-highlight. You can put it somewhere in your $PATH. Or get it from diff-so-fancy project.

2

By definition, diff is showing differences lines by lines (see diff manual page), it will therefore not show only the differing characters. You can reduce the amount of difference by pre-processing the files, for exemple by inserting an end-of-line character after each semi-column:

sed -e 's/;/;\'$'\n/g' old.sql > old.patched

Then use diff on the two resulting files.

1
  • This might not be a great idea unless you can guarantee there's nothing like: Insert into table1 values (101 ,"a;", "xyz;"); Commented Jan 21, 2021 at 16:11
0

I also wrote my own script to solve this problem using the Longest common subsequence algorithm.

It is executed as such

JLDiff.py a.txt b.txt out.html

The result is in html with red and green coloring. Larger files do exponentially take a longer amount of time to process but this does a true character-by-character comparison without checking line by line first.

This answer is cross-posted from here.

I have found that JLDiff runs a lot faster under pypy.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .