- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Searching multiple columns for a row match in MySQL
For this, use UNION. Let us first create a table −
mysql> create table DemoTable645 (Id int,FirstName varchar(100)); Query OK, 0 rows affected (0.67 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable645 values(100,'Chris'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable645 values(101,'Robert'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable645 values(101,'Bob'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable645 values(102,'Carol'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable645 values(100,'John'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable645 values(100,'Robert'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable645 values(101,'Mike'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable645 values(101,'John'); Query OK, 1 row affected (0.14 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable645;
This will produce the following output −
+------+-----------+ | Id | FirstName | +------+-----------+ | 100 | Chris | | 101 | Robert | | 101 | Bob | | 102 | Carol | | 100 | John | | 100 | Robert | | 101 | Mike | | 101 | John | +------+-----------+ 8 rows in set (0.00 sec)
Following is the query to search multiple columns −
mysql> select Id AS Records from DemoTable645 where Id LIKE '%100%' union select FirstName from DemoTable645 where FirstName LIKE '%John%' order by 1 LIMIT 3;
This will produce the following output −
+---------+ | Records | +---------+ | 100 | | John | +---------+ 2 rows in set (0.00 sec)
- Related Articles
- Update multiple columns of a single row MySQL?
- Concatenate multiple rows and columns in a single row with MySQL
- Count value for multiple columns in MySQL?\n
- Set multiple values for custom columns in MySQL?
- MySQL Select Statement DISTINCT for Multiple Columns?
- How to check multiple columns for a single value in MySQL?
- Count multiple rows and display the result in different columns (and a single row) with MySQL
- MySQL multiple COUNT with multiple columns?
- Change multiple columns in a single MySQL query?
- How to check for duplicates in MySQL table over multiple columns?
- MySQL filtering by multiple columns?
- How to search multiple columns in MySQL?
- Searching for an integer value in a varchar field in MySQL?
- Set search feature in MySQL for full text searching
- How to alter multiple columns in a single statement in MySQL?

Advertisements