Looking for a way to filter your rows from a SQL, MySQL or MariaDB database table by identical columns? Looking for a Query to play on console?
I recently went through a situation where it was necessary to filter on a database table and only display rows that had two columns with the same text values. After hours of searching I found the simple solution and I'm sharing it with you.
I'm no programmer, and I believe that most people looking for these tips don't have that much sense of code either.
I often use the Navicat program to edit databases, it is quite fast and practical, in addition to allowing you to edit rows and columns with equal freedom to Microsoft Excel or Apple Numbers.
Most people use PhpmyAdmin to manage their website's databases. Both allow you to run scripts through the SQL or Query tab.
Using SQL Query to filter
As I'm no database professional, I didn't know that you can display filtered tables through a query code, but luckily I found out.
My use in the database is entirely to delete lines, look for texts and use the Search and Replace, I had no idea that SQL codes are super useful.
I have personally used these codes to check for translation errors and find untranslated rows in the database tables of translate press.
SQL Query code
The codes below are useful for:
- Find equal rows from different columns;
- Find different rows from different columns;
- Finds equal content in two columns;
SELECT * FROM `databasename`.`tablename` WHERE colum01 = colum02 ORDER BY `id` LIMIT 500 OFFSET 0; The code we just looked at uses WHERE colum01 = colum02 to display rows that have the same results in both columns. The code can be extended to more columns if desired.
In case you want to add the traditional filters to find certain text you can extend the script before ORDER BY putting a AND as in the code below:
SELECT * FROM `databasename`.`tablename` WHERE colum01 = colum02 AND (`colum01` LIKE '%text%') ORDER BY `id` LIMIT 500 OFFSET 0; After AND we have a code in parentheses that filters the text only in column 01. LIKE to find lines with containing the filtered value or NOT LIKE to find rows that do not contain the value.
The script can be extended further using AND and other code. Now to finish we are going to display another code, but instead of displaying rows with equal values, it displays rows with different values using !=.
SELECT * FROM `databasename`.`tablename` WHERE colum01 != colum02 ORDER BY `id` LIMIT 500 OFFSET 0; Hope this code helps you to really filter and search for rows with the same or different values from separate columns using conventional filters LIKE and NOT LIKE.