Find duplicate records in MySQL

September 17, 2012 - 5:19pm - by admin

So, the task is to get duplicate records from a MySQL database.

The easy way:

SELECT COUNT(*), column1, column2 FROM tablename

GROUP BY column1, column2

HAVING COUNT(*)>1;

More complex case: shows each duplicated row:

It can be done using subquery:

SELECT firstname, lastname, list.address FROM list

INNER JOIN (SELECT address FROM list

GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.address

or with INNER JOIN:

SELECT a.firstname, a.lastname, a.address

FROM list a

INNER JOIN list b ON a.address = b.address

WHERE a.id <> b.id

If the same 'address' exist more than two times, then DISTINCT is needed.


---

Find (search) and replace text from command line in multiple files (Linu

August 11, 2012 - 4:18pm - by admin

Another (and more easy) way to change text in multiple files is to use grep:

grep -lr -e 'oldtext' * | xargs sed -i 's/oldtext/newtext/g'

or to use PERL:

perl -p -i -e ’s/oldtext/newtext/g’ *

 

---

Find (search) and replace text from command line in multiple files (Linu

August 11, 2012 - 3:23pm - by admin

Just after I posted this article the second more easy solution has been found. Here it is:

Find (search) and replace text from command line in multiple files (Linux) #2

When you are working on the Linux command line and you come across a large file or a large number of files in which you need to replace a certain text with another, finding and pasting over each instance of the text can be a bit time consuming. Well, worry no more. Linux has just the solution for you. Here’s a way to find and replace a string of text in one or more files automatically.

For the purpose of this exercise we will use a Linux command line tool called “sed”.  ”sed” is a very powerful and versatile tool, and a lot can be written about its capabilities. We are using a very limited aspect of “sed” here. I would definitely recommend that you read up a little more on “sed” if you find this aspect of it interesting.

We are going to use the following syntax to find and replace a string of text in a file:

# sed -i 's/[orginal_text]/[new_text]/' filename.txt

Say you have a file called “database.txt” with numerous instances of the IP address of your database server in it. You have just switched to a new database server and need to update it with the new server’s IP address. The old IP address is 192.168.1.16 and the new one is 192.168.1.22. Here’s how you go about it:

# cat database.txt

LOCAL_DATABASE = 192.168.1.16

LOCAL_DIR = /home/calvin/

PROD_DB = 192.168.1.16

# sed -i 's/192.168.1.16/192.168.1.22/g' database.txt

# cat database.txt

LOCAL_DATABASE = 192.168.1.22

LOCAL_DIR = /home/calvin/

PROD_DB = 192.168.1.22

Now open the file “database.inc” and check to see if the new IP address has taken place of your old one. Here’s the breakup of the above command. First you call the “sed” command. Then you pass it the parameter “-s” which stands for “in place of”. Now we use a little bit of regular expressions, commonly known as “regex”  for the next bit. The “s” in the quoted string stands for “substitute”, and the “g” at the end stands for “global”. Between them they result in a “global substitution of the the string of text you place in between them.

You can optionally skip the “g” at the end. This means that the substitution will not be global, which practically translates to the substitution of only the first instance of the string in a line. So if you had a line with multiple instances of the text you are trying to replace, here’s what will happen

# cat database.txt

LOCAL_DATABASE = 192.168.1.16

LOCAL_DIR = /home/calvin/

PROD_DB = 192.168.1.16, 192.168.1.16

# sed -i 's/192.168.1.16/192.168.1.22/' database.txt

# cat database.txt

LOCAL_DATABASE = 192.168.1.22

LOCAL_DIR = /home/calvin/

PROD_DB = 192.168.1.22, 192.168.1.16

Here comes the real magic. Now, say you want to change a string of text not just in a single file, but in the entire directory you are in. There are a number of text files in which you need to find and replace the “wine” with “champagne”.

# find . -maxdepth 1 -name "*.txt" -type f -exec sed -i 's/wine/champagne/' {} \

We use the find command to get a list of all the text files in the current directory. That’s the “find . -maxdepth 1 -name “*.txt” -type f” part. “find . maxdepth 1″ tell the computer to look in the current directory and go no deeper than the current directory. The ‘-name  ”*.txt”‘ part tells find to only list files with the extension of “.txt”. Then the “-type f” section specifies that “find” should only pick exactly matching files. Finally the “-exec” part tells “find” to execute the command that follows, which, in this case, is the “sed” command to replace the text – “sed -i ‘s/wine/champagne/’ {} \”.

I realize that the above command seems complicated. However, once you use it a little bit you will realize that it is probably worth noting it down and using it. Now try changing a string of text in multiple levels of directories.

---