Search for a string within text column in MySQL?

You can search for a string within text column in MySQL with the help of LIKE clause. The syntax is as follows −

select *from yourTableName where yourColumnName like '%anyStringValue%';

To use the above syntax, let us first create a table −

mysql> create table SearchTextDemo
   −> (
   −> BookName TEXT
   −> );
Query OK, 0 rows affected (0.55 sec)

Insert some strings in the table. The query is as follows −

mysql> insert into SearchTextDemo values('Let us C');
Query OK, 1 row affected (0.28 sec)

mysql> insert into SearchTextDemo values('C in Depth');
Query OK, 1 row affected (0.14 sec)

mysql> insert into SearchTextDemo values('C in Depth with Data Structure');
Query OK, 1 row affected (0.18 sec)

mysql> insert into SearchTextDemo values('Introduction to Java');
Query OK, 1 row affected (0.17 sec)

mysql> insert into SearchTextDemo values('Java in Depth');
Query OK, 1 row affected (0.13 sec)

Display all records with the help of select statement. The query is as follows −

mysql> select *from SearchTextDemo;

The following is the output −

+--------------------------------+
| BookName                       |
+--------------------------------+
| Let us C                       |
| C in Depth                     |
| C in Depth with Data Structure |
| Introduction to Java           |
| Java in Depth                  |
+--------------------------------+
5 rows in set (0.00 sec)

Here is the query to search a specific string within text column −

mysql> select *from SearchTextDemo where BookName like '%in Depth%';

The following is the output −

+--------------------------------+
| BookName                       |
+--------------------------------+
| C in Depth                     |
| C in Depth with Data Structure |
| Java in Depth                  |
+--------------------------------+
3 rows in set (0.00 sec)

Look at the above sample output, the string ‘in Depth’ has been searched from column name ‘BookName’ with data type TEXT.

Jennifer Nicholas

Published on 10-Jan-2019 12:26:57

• Related Questions & Answers
• Search for specific characters within a string with MySQL?
• How to search a column for an exact string in MySQL?
• Search for text between delimiters in MySQL?
• MySQL query to search within the last 5 characters in a column?
• Quickly search for a string in MySQL database?
• Set search feature in MySQL for full text searching
• How to search a MySQL table for a specific string?
• How to search for exact string in MySQL?
• Search text containing a new line in MySQL?
• With JavaScript RegExp how to search a string for text that matches regexp?
• Search for a text in MongoDBs Double Nested Array?
• Adding/ concatenating text values within a MySQL SELECT clause?
• Find integer within +/- 1 from a column in MySQL
• MySQL query to conduct a basic search for a specific last name in a column
• How to search for the exact string value in MySQL?