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 of the database is entirely for deleting rows, searching for texts, and using the Search and Replace; I had no idea that SQL codes are super useful.
I personally used these codes to check for translation errors and find lines that were not translated in the database tables of Translatepress.
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 saw uses WHERE colum01 = colum02 to display rows that have equal results in both columns. The code can be extended to more columns if desired.
If you want to add traditional filters to find specific text, you can extend the script before ORDER BY by adding an AND as shown in the code below:
SELECT * FROM `databasename`.`tablename` WHERE colum01 = colum02 AND (`colum01` LIKE '%text%') ORDER BY `id` LIMIT 500 OFFSET 0; After the AND we have a code in parentheses that filters the text only in column 01. LIKE is used to find rows 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; I hope this code helps you to actually filter and search for rows with values equal to or different from separate columns using conventional LIKE and NOT LIKE filters.