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

Previous Page Print Page