Find out if a varchar contains a percent sign in MySQL?

To find out a varchar contains a percent sign in MySQL, you can use LIKE operator. The syntax is as follows −

SELECT * FROM yourTableName WHERE yourColumnName like '%|%%' escape '|';

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table FindPercentInVarcharDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Words varchar(30),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.51 sec)

Insert some records with % sign using insert command. The query is as follows −

mysql> insert into FindPercentInVarcharDemo(Words) values('This is a My%SQL Program');
Query OK, 1 row affected (0.19 sec)

mysql> insert into FindPercentInVarcharDemo(Words) values('Java is an object oriented');
Query OK, 1 row affected (0.19 sec)

mysql> insert into FindPercentInVarcharDemo(Words) values('C# is also an object%oriented');
Query OK, 1 row affected (0.57 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from FindPercentInVarcharDemo;

The following is the output −

+----+-------------------------------+
| Id | Words                         |
+----+-------------------------------+
| 1  | This is a My%SQL Program      |
| 2  | Java is an object oriented    |
| 4  | C# is also an object%oriented |
+----+-------------------------------+
3 rows in set (0.00 sec)

The following is the query to find out varchar contains % sign. The query is as follows −

mysql> select *from FindPercentInVarcharDemo where Words like '%|%%' escape '|';

The following is the output displaying only the values with a % sign −

+----+-------------------------------+
| Id | Words                         |
+----+-------------------------------+
| 1 | This is a My%SQL Program       |
| 4 | C# is also an object%oriented  |
+----+-------------------------------+
2 rows in set (0.00 sec)

Ankith Reddy

Published on 01-Feb-2019 10:55:11

Previous Page Print Page